Help required with GROUP BY

Question

Help required with GROUP BY

faulknerwilliam2

SSCrazy

Points: 2299
More actions
August 9, 2018 at 5:42 am

#407696

Hello
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.
Any advice would be very gratefully received.
Many thanks.

Viewing 4 posts - 1 through 3 (of 3 total)

You must be logged in to reply to this topic. Login to reply

Luis Cazares SSC Guru Points: 183698 More actions · Answer 1

faulknerwilliam2 - Thursday, August 9, 2018 5:42 AM
Hello
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.
Any advice would be very gratefully received.
Many thanks.

You're basically telling that you want each combination of three columns: WorkTray,RefNo, NameofItem.
You might need to remove RefNo and NameofItem from the GROUP BY clause and either remove them from the column list or use an aggregate function for them.

Luis C.
General Disclaimer:
Are you seriously taking the advice and code from someone from the internet without testing it? Do you at least understand it? Or can it easily kill your server?

How to post data/code on a forum to get the best help: Option 1 / Option 2

faulknerwilliam2 SSCrazy Points: 2299 More actions · Answer 2

Luis Cazares - Thursday, August 9, 2018 6:21 AM
faulknerwilliam2 - Thursday, August 9, 2018 5:42 AM
Hello
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.
Any advice would be very gratefully received.
Many thanks.
You're basically telling that you want each combination of three columns: WorkTray,RefNo, NameofItem.
You might need to remove RefNo and NameofItem from the GROUP BY clause and either remove them from the column list or use an aggregate function for them.

Thanks for the answer Luis, yes you're right. Solved via a CTE:

WITH cte

as

(

SELECT WorkTray, Date AS [OldestDate], RefNo, NameofItem,

row_number() over (partition by WorkTray order by WorkTray,RefNo, NameofItem) RN

FROM ...

GROUP BY WorkTray,RefNo, NameofItem

) select * from cte where rn=1

sgmunson SSC Guru Points: 110639 More actions · Answer 3

faulknerwilliam2 - Thursday, August 9, 2018 7:03 AM
Luis Cazares - Thursday, August 9, 2018 6:21 AM
faulknerwilliam2 - Thursday, August 9, 2018 5:42 AM
Hello
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.
Any advice would be very gratefully received.
Many thanks.
You're basically telling that you want each combination of three columns: WorkTray,RefNo, NameofItem.
You might need to remove RefNo and NameofItem from the GROUP BY clause and either remove them from the column list or use an aggregate function for them.
Thanks for the answer Luis, yes you're right. Solved via a CTE:
WITH cte
as
(
SELECT WorkTray, Date AS [OldestDate], RefNo, NameofItem,
row_number() over (partition by WorkTray order by WorkTray,RefNo, NameofItem) RN
FROM ...
GROUP BY WorkTray,RefNo, NameofItem
) select * from cte where rn=1

If you want the "oldeest" item in each tray, wouldn't you have to order by the Date column ?

Steve (aka sgmunson) 🙂 🙂 🙂
Rent Servers for Income (picks and shovels strategy)