Sql query to return collated results based upon two columns

Question

0.00/5 (No votes)

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:

SQL

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

raddevus

Updated 16-May-18 9:17am

v2

Add a Solution

1 solution

Add a Solution

Add your solution here

Treat my content as plain text, not as HTML

Preview 0

…

Existing Members

Sign in to your account

...or Join us

Download, Vote, Comment, Publish.

Your Email
Password
Forgot your password?

Your Email
This email is in use. Do you need your password?
Optional Password

I have read and agree to the Terms of Service and Privacy Policy
Please subscribe me to the CodeProject newsletters

When answering a question please:

Read the question carefully.
Understand that English isn't everyone's first language so be lenient of bad spelling and grammar.
If a question is poorly phrased then either ask for clarification, ignore it, or edit the question and fix the problem. Insults are not welcome.
Don't tell someone to read the manual. Chances are they have and don't get it. Provide an answer or move on to the next question.

Let's work to help developers, not make them feel stupid.

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

raddevus · Accepted Answer · 2018-05-16T09:18:00

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:

SQL

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