CODEWITHSUNDEEP
t-sql
Will F
Will F

Reputation: 437

Grouping in a report

I am using t-sql.

I have 4 work trays and I would like a report that gives me the name of each work tray, plus the oldest item of post in it, plus a couple more fields. It needs to be limited to 4 rows - one for each work tray.

So at the moment I have this:

SELECT WorkTray, MIN(Date) AS [OldestDate], RefNo, NameofItem

FROM ...

GROUP BY WorkTray,RefNo, NameofItem

ORDER BY WorkTray,RefNo, NameofItem

However when I run this it gives me every item in each work tray, eg a report 100s of items long - I just want it to be limited to 4 rows of data, one for each work tray:

Work Tray    Date                    RefNo                    NameofItem

A                 1/2/15                25                          Outstanding Bill

B                 5/5/18                1000                      Lost post

C                 2/2/12               17                          Misc

D                 6/12/17              876                       Misc

So I'm sure I'm going wrong somewhere with my GROUP BY - but I can't see where.

Upvotes: 0

Views: 32

Answers (1)

scratt
scratt

Reputation: 190

There is a trick for doing this that has been answered on stackoverflow before. Here it is adapted to your query:

SELECT *
FROM 
(SELECT WorkTray, Date AS [OldestDate], RefNo, NameofItem, ROW_NUMBER() OVER (PARTITION BY WorkTray ORDER BY WorkTray, [Date]) AS rn
FROM MyTable
) GroupedByTray
WHERE rn = 1

The PARTITION BY tells it to count the rows for each type of tray, and the ORDER BY works similar to the normal ORDER BY clause. Assuming you have only 4 work trays (A - D), the "WHERE rn = 1" part will return only the first row for WorkTrays A - D.

Upvotes: 1

Related Questions