Suppose I have a table with the following columns:
Id, ConnectionId, File, FileCreated
int, int, varchar, DateTime
Data Example
Further, suppose it is filled with unsorted data like the following:
1, 3, "a.dat", 2018-05-16 13:53:40.008
2, 1, "b.dat", 2018-05-16 13:53:40.007
3, 3, "c.dat", 2018-05-16 13:53:40.009
4, 3, "z.dat", 2018-05-16 13:53:40.002
5, 3, "h.dat", 2018-05-16 13:53:40.003
6, 2, "ba.dat", 2018-05-16 13:53:40.004
7, 3, "zy.dat", 2018-05-16 13:53:40.005
8, 1, "f.dat", 2018-05-16 13:53:40.001
9, 1, "cd.dat", 2018-05-16 13:53:40.006
How might I query this data so that the dataset returns me :
1. one row for each of the connections
2. ordered in time ascending order
Expected Results
The result I'm looking for would be the following:
(Note: I'm putting these in groups so you can see that they are returned based upon ConnectionId first (like group by maybe) ).
8, 1, "f.dat", 2018-05-16 13:53:40.001
4, 3, "z.dat", 2018-05-16 13:53:40.002
6, 2, "ba.dat", 2018-05-16 13:53:40.004
5, 3, "h.dat", 2018-05-16 13:53:40.003
9, 1, "cd.dat", 2018-05-16 13:53:40.006
7, 3, "zy.dat", 2018-05-16 13:53:40.005
2, 1, "b.dat", 2018-05-16 13:53:40.007
1, 3, "a.dat", 2018-05-16 13:53:40.008
3, 3, "c.dat", 2018-05-16 13:53:40.009
Hopefully my example shows you that these are not strictly time-based and not strictly connectionId based either. It is both.
What I have tried:
select connectionid, fileCreated from Table
group by connectionid, filecreated
Returns only 3 rows but there are 9 rows so mine is wrong.