Click here to Skip to main content
13,665,641 members
Rate this:
 
Please Sign up or sign in to vote.
See more:
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.
Posted 16-May-18 9:04am
Updated 16-May-18 9:17am
v2

1 solution

Rate this: bad
 
good
Please Sign up or sign in to vote.

Solution 1

Okay, someone at another site solved this.
I am absolutely amazed because the answer that was provided gave me the EXACT results that I asked for. Wow!! It's quite an interesting query.

Here's what the answer looked like:
DECLARE @mockup TABLE(Id INT,ConnectionId INT,[File] VARCHAR(100),FileCreated DATETIME2);
INSERT INTO @mockup VALUES
 (1, 3, 'a.dat','2018-05-16T13:53:40.008')
,(2, 1, 'b.dat','2018-05-16T13:53:40.007')
,(3, 3, 'c.dat','2018-05-16T13:53:40.009')
,(4, 3, 'z.dat','2018-05-16T13:53:40.002')
,(5, 3, 'h.dat','2018-05-16T13:53:40.003')
,(6, 2, 'ba.dat','2018-05-16T13:53:40.004')
,(7, 3, 'zy.dat','2018-05-16T13:53:40.005')
,(8, 1, 'f.dat','2018-05-16T13:53:40.001')
,(9, 1, 'cd.dat','2018-05-16T13:53:40.006');

SELECT *
FROM @mockup
ORDER BY ROW_NUMBER() OVER(PARTITION BY ConnectionId ORDER BY FileCreated)
        ,FileCreated;


The results looked like this:
Id	ConnectionId	File	FileCreated
8	1	       f.dat	2018-05-16 13:53:40.0010000
4	3	       z.dat	2018-05-16 13:53:40.0020000
6	2	       ba.dat	2018-05-16 13:53:40.0040000
5	3	       h.dat	2018-05-16 13:53:40.0030000
9	1	       cd.dat	2018-05-16 13:53:40.0060000
7	3	       zy.dat	2018-05-16 13:53:40.0050000
2	1	       b.dat	2018-05-16 13:53:40.0070000
1	3	       a.dat	2018-05-16 13:53:40.0080000
3	3	       c.dat	2018-05-16 13:53:40.0090000
  Permalink  
v2
Comments
Richard Deeming 17-May-18 12:08pm
   
Question posted 21 hours ago; answer posted 21 hours ago.

Oh, come on! You didn't even give us a chance to answer this. :D

(Also, your question said you only wanted one row per connection, but that result seems to have multiple rows per connection.)

This content, along with any associated source code and files, is licensed under The Code Project Open License (CPOL)

  Print Answers RSS
Top Experts
Last 24hrsThis month


Advertise | Privacy | Cookies | Terms of Service
Web01-2016 | 2.8.180810.1 | Last Updated 16 May 2018
Copyright © CodeProject, 1999-2018
All Rights Reserved.
Layout: fixed | fluid

CodeProject, 503-250 Ferrand Drive Toronto Ontario, M3C 3G8 Canada +1 416-849-8900 x 100