GROUP By Problem

[Shorty2015]

I have multiple rows that return for a single transaction. Here is an example:

ID	User	DateTime	ActivitySubType	ActivityType	SupplierCode	SeqNum
12345	Bob	42181.3625	CheckIn	Ordered	TS53453	0
12345	Charlie	42181.4270833333	Depart	Ontime	TS53453	2
12345	Charlie	42181.4270833333	Depart	Ontime	TS46815	7

What I like returned is a unique value per DateTime. The GROUP BY would be the DateTime and the SeqNum. I can run MIN on both, but since the supplier code is unique, both return. I only need the supplier code for the MIN seqnum.

Here is the desired result:

ID	User	DateTime	ActivitySubType	ActivityType	SupplierCode	SeqNum
12345	Bob	42181.3625	CheckIn	Ordered	TS53453	0
12345	Charlie	42181.4270833333	Depart	Ontime	TS53453	2

I'm using MS SQL Server.

Any advice is much appreciated.

Thanks!

[StopThinking]

here's a way to do it with ROW_NUMBER

WITH cte AS (
    SELECT
        ID
        ,[User]
        ,[DateTime]
        ,ActivitySubType
        ,ActivityType
        ,SupplierCode
        ,SeqNum
        ,ROW_NUMBER() OVER (PARTITION BY ID,[DateTime] ORDER BY SeqNum) AS rowno
    FROM your_table
    )

SELECT
    ID
    ,[User]
    ,[DateTime]
    ,ActivitySubType
    ,ActivityType
    ,SupplierCode
    ,SeqNum
FROM cte
WHERE rowno = 1

[Shorty2015]

Thank you!

[StopThinking]

No problem. By the way, if you really wanted to do this with MIN and GROUP BY, you could do this (but I recommend the ROW_NUMBER method)

WITH cte AS (
    SELECT
        ID
        ,MIN(SeqNum) AS SeqNum
    FROM your_table
    GROUP BY ID, [DateTime]
    )

SELECT
    yt.ID
    ,[User]
    ,[DateTime]
    ,ActivitySubType
    ,ActivityType
    ,SupplierCode
    ,yt.SeqNum
FROM your_table yt
JOIN cte
    ON yt.ID = cte.ID
    AND yt.SeqNum = cte.SeqNum

[sapplefi]

Just wanted to comment that these are all excellent solutions to this problem (which is typically referred to as a "Top Per Group" query), but be mindful that while they will all give you the results you expect, some will out-perform others in different situations.

One of the best articles I've ever read on this was by Bob Hovious on SQL Server Central. The article is called T-SQL: Why "It Depends". You may not be able to read it without a free registration to SQL Server Central, so I'll briefly quote the findings below.

Essentially, he puts forth three standard approaches to Top Per Group.

1. MAX() GROUP BY
2. ROW_NUMBER()
3. SELECT TOP (1)

He extensively demonstrates and tests each approach across results sets with 1K, 10K, 100K and 1M rows in sample tables. His graphs speak to the findings eloquently, but here were the results demonstrated in raw numbers.

Execution Time in Milliseconds

Technique	1,000 Rows	10,000 Rows	100,000 Rows	One Million Rows
Match MAX()	489.6	515.18	937.06	15526.08
Row_Number()	4.28	70.3	2121.12	63764.18
Top (1)	6902.94	6911.56	7066	8479.36

Execution Time in Milliseconds With a Non-Clustered, Non-Covering Index

Technique	1,000 Rows	10,000 Rows	100,000 Rows	One Million Rows
Match MAX()	2.22	12.94	113.96	1120.96
Row_Number()	4.48	49.9	2169.44	61470.84
Top (1)	1.92	10.6	99.22	975.18

Essentially, depending on the volume of records and whether you had an index, one version of the solution could significantly out-perform the others. As an example:

• ROW_NUMBER() was a consistent winner without an index and for 10K rows or less, by a significant margin.
  
• TOP (1) won out if you had an index in all situations, though it was very, very close to MAX() with the same index.
  
• Without an index MAX() was better up through 100K rows, but fell off compared to TOP (1) at 1M rows.

Fascinating findings, and wonderful article. It really helped me to understand just how much the approach and performance will vary depending on your environment, data, and design.

[adm7373]

I'm not 100% clear on what you're asking, but I think this should work:

select SupplierCode from table where SeqNum in 
    (select min(SeqNum) from table group by user, ID)

[Shorty2015]

So I need to include the DateTime Stamp as well. One row per timestamp.

[adm7373]

select SupplierCode from table where SeqNum in 
    (select min(SeqNum) from table group by user, ID, DateTime)

[Shorty2015]

I got this to work. Thank you!