Help required with GROUP BY

  • 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.

  • 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
  • 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

  • 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 ?

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

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