Retaining additional Columns whilst using MIN or MAX with Group By-CodePudding

First time post on stackoverflow, new to SQL (and any code in general) would appreciate some advice on a group by query I have. Based on the sample data set below I am looking to group on ID/Name and return the Min sales for each person. The difficulty I'm having is that I also want to return the REGION and QTR information associated with the lowest sales performance.

ID	NAME	REGION	SALES	QTR.
1	Luke	NORTH	45	1
2	Danny	WEST	67	2
3	Elle	NORTH	73	1
1	Luke	WEST	32	4
2	Danny	EAST	22	3
2	Danny	EAST	18	2

So essentially group to a table as below

ID	NAME	REGION	MIN SALES	QTR.
1	Luke	WEST	32	4
2	Danny	EAST	18	2
3	Elle	NORTH	73	1

If I bring in the additional columns into the group by it creates multiple rows for each individual, if I leave them out I lose the associated data.

Can anyone help, from looking online it looks like I might have to join the table back with itself, though I'm not sure how to do that.

Thanks

Tom

CodePudding user response：

https://dbfiddle.uk/?rdbms=oracle_18&fiddle=ebb85bdb1b93e668aadc688c18351746

  with data(ID, NAME,   REGION, SALES,  QTR) as
    (select 1,  'Luke', 'NORTH',45, 1 from dual union all
    select 2,   'Danny','WEST', 67, 2 from dual union all
    select 3,   'Elle', 'NORTH',73, 1 from dual union all
    select 1,   'Luke', 'WEST', 32, 4 from dual union all
    select 2,   'Danny','EAST', 22, 3 from dual union all
    select 2,   'Danny','EAST', 18, 2 from dual 
    ),
    minS as (select ID , min(Sales) minsales
    from data
    group by ID)
    Select data.id, data.name,data.region,data.sales "min sales" ,data.qtr from data
    join minS on minS.ID= data.Id
    and minS.minsales= data.sales
    order by data.id

CodePudding user response：

Calculate a row_number in a sub-query, then filter on it.

SELECT ID, NAME, REGION, SALES AS MIN_SALES, QTR
FROM
(
    SELECT ID, NAME, REGION, SALES, QTR
    , ROW_NUMBER() OVER (PARTITION BY ID ORDER BY SALES ASC) AS rn
    FROM YourTable
) q
WHERE rn = 1
ORDER BY id

And if you want MAX_SALES, then order by sales DESC (descending) in the row_number.