Get additional columns when grouping

Question

I am trying to add one more column into this search, but it brings make multiple lines when I add the next column

The code below brings back the correct information, but I need the column named category without bringing back multiple rows for each bus

SELECT workorders.bus, MAX(workorders.service_date) AS ServiceDate
  , MAX (workorders.mileage) AS Mileage
FROM dbo.WorkOrders 
WHERE (workorders.type = 'PMI') 
GROUP BY workorders.bus
order by workorders.bus

bus ServiceDate Mileage
108 2017-05-14  347480
120 2014-07-11  167900
545 2017-03-13  280659
546 2017-03-13  293888
547 2015-05-12  249017
558 2019-06-24  376046

I expect to see a category next to each of these mileages, like A on the first return from bus 108. But, when I add the category to the search, then I get five bus 108s.

Possible duplicate of [Get top 1 row of each group](https://stackoverflow.com/questions/6841605/get-top-1-row-of-each-group) — Dale K, Jul 03 '19 at 03:02

score 0 · Answer 1 · answered Jul 03 '19 at 02:49

0

Just use an aggregation function:

SELECT wo.bus,  MAX(wo.service_date) AS ServiceDate, MAX(wo.mileage) AS Mileage,
       MAX(wo.category) as category
FROM dbo.WorkOrders wo
WHERE wo.type = 'PMI'
GROUP BY wo.bus
ORDER BY wo.bus

answered Jul 03 '19 at 02:49

Gordon Linoff

1,242,037
58
646
786

That brings back the max category, I need the category from that row. This is an inspection list for doing preventative maintenance on buses. the category shows which level of inspection was done last. When I use MAX then it brings the highest level, not the level that was done on that day – Jul 03 '19 at 02:53

score 0 · Answer 2 · edited Aug 30 '20 at 23:00

0

Add the category in the group by and use the row_number to add the row id in order manner.

/****** Script for SelectTopNRows command from SSMS  ******/
SELECT *
FROM
(
    SELECT [bus]
        , [Category]
        , MAX([Servicedate]) AS [Servicedate]
        , MAX([mileage]) AS [mileage]
        , ROW_NUMBER() OVER (PARTITION BY [bus] ORDER BY max([mileage]) desc) rowID
    FROM [tbltest]
    GROUP BY bus, category
) tmp
WHERE tmp.rowID = 1

edited Aug 30 '20 at 23:00

Dale K

25,246
15
42
71

answered Jul 03 '19 at 03:07

Vic

457
1
6
23

bus category ServiceDate Mileage 108 A 2017-05-14 347480 108 B 2016-10-08 337818 108 C 2017-03-30 345266 That brings all of the categories. I want the category from the most recent date – Jul 03 '19 at 03:18
Do you mean you want to get the highest mileage in bus 108 which is category A. Is my Interpretation is Correct? – Vic Jul 03 '19 at 03:26

Get additional columns when grouping

2 Answers2