how to compare rows in the same sql table and get maximum value

Question

I have a table as follows

ID | Status ID | Value 
1  |   1       |  100
2  |   1       |  200
3  |   1       |  300
4  |   2       |  100 
5  |   2       |  150
6  |   2       |  200
7  |   3       |  500
8  |   3       |  300
9  |   3       |  150

I need to get the maximum value within the status. so my result should look like the following

ID | Status ID | Value 
3  |   1       |  300
6  |   2       |  200
7  |   3       |  500

I'm fairly new to SQL and would appreciate your inputs

http://stackoverflow.com/questions/612231/how-can-i-select-rows-with-maxcolumn-value-distinct-by-another-column-in-sql — Deus Levin, Sep 02 '15 at 03:32

score 2 · Accepted Answer · answered Sep 02 '15 at 03:46

create table #temp(id int, statusid int, value int)
insert #temp(id,statusid,value)
select 1,1,100
union select 2,1,200
union select 3,1,300
union select 4,2,100 
union select 5,2,150
union select 6,2,200
union select 7,3,500
union select 8,3,300
union select 9,3,150

-- if you don't need the id
select statusid, max(value)
from #temp
group by statusid

-- if you need the id
select min(id), X.statusid, X.value
from (
    select statusid, max(value) value
    from #temp
    group by statusid
) X
inner join #temp T
on X.statusid = T.statusid
and X.value = T.value
group by X.statusid, X.value

Thanks. union select inserts is cool too. good to know. . – user2180794 Sep 02 '15 at 04:36 — user2180794, Sep 02 '15 at 04:36

score 1 · Answer 2 · answered Sep 02 '15 at 03:46

1

Give this a go:

SELECT t.*
FROM TEST t
INNER JOIN (
    SELECT STATUS,
           MAX(VALUE) AS MAX_VALUE
    FROM TEST
    GROUP BY STATUS) gt
ON t.STATUS = GT.STATUS
AND t.VALUE = gt.MAX_VALUE;

answered Sep 02 '15 at 03:46

Andrew LaPrise

3,373
4
32
50

score 0 · Answer 3 · answered Sep 02 '15 at 03:38

0

you can write your query as:

select * from data_table dt1
where dt1.value >= ALL (select value from data_table dt2 where dt2.Status = dt1.Status)

answered Sep 02 '15 at 03:38

Mahmoud

883
7
17

Utsav · Answer 4 · 2015-09-02T03:57:54.027

I changed your column names a bit, but it will work the way you wanted.

   with tbl ( ID,StatusID,Val)as
              (select 1,1,100 from dual union
               select 2,1,200 from dual union
               select 3,1,300 from dual union
               select 4,2,100 from dual union
               select 5,2,150 from dual union
               select 6,2,200 from dual union
               select 7,3,500 from dual union
               select 8,3,300 from dual union
               select 9,3,150 from dual
      )          
     select T1.id,T1.statusid,T2.val
     from tbl T1,
     (SELECT statusid,max(val) AS val 
     FROM tbl GROUP BY statusid) T2
     WHERE T1.statusid=T2.statusid
     AND T1.val=T2.val

Output

    ID     StatusID    Val
    3        1         300
    6        2         200
    7        3         500

DECLARE @a TABLE ( ID int, StatusId int, Value int ) INSERT INTO @a ( ID, StatusId, Value ) VALUES ( 1 , 1 , 100) ,(2 , 1 , 200) ,(3 , 1 , 300) ,(4 , 2 , 100 ) ,(5 , 2 , 150) ,(6 , 2 , 200) ,(7 , 3 , 500) ,(8 , 3 , 300) ,(9 , 3 , 1500); with toselect as ( select Id, StatusId , Value, Row_Number() over (partition by StatusId Order by value desc) as Count from @a ) select Id, StatusId, Value from toselect where Count = 1 — Learner, Sep 02 '15 at 05:28

score 0 · Answer 5 · answered Sep 02 '15 at 03:55

DECLARE @a TABLE
(
    ID  int,
    StatusId int,
    Value  int
)

INSERT INTO @a
        ( ID, StatusId, Value )
VALUES  ( 1 ,   1      ,  100)
        ,(2 ,   1      ,  200)
        ,(3 ,   1      ,  300)
        ,(4 ,   2      ,  100 )
        ,(5 ,   2      ,  150)
        ,(6 ,   2      ,  200)
        ,(7 ,   3      ,  500)
        ,(8 ,   3      ,  300)
        ,(9 ,   3      ,  150)


SELECT 
    a.id, b.StatusId, b.Value
FROM 
    @a a INNER JOIN
    (
        SELECT 
            StatusId, MAX(Value) Value 
        FROM 
            @a
        GROUP BY
            StatusId
    ) b ON b.StatusId = a.StatusId AND b.Value = a.Value
ORDER BY 
    a.ID

score 0 · Answer 6 · answered Sep 02 '15 at 03:56

For SQL Server, it's something simple like this:

select distinct 
FIRST_VALUE(ID) over (partition by Status order by Value desc) ID, 
Status, 
MAX(Value) over (partition by Status) Value
from tableName

Also, recommend you don't use reserved words like ID or Status or Value. Sure, SQL Server will figure it out, but it's bad practice.

how to compare rows in the same sql table and get maximum value

6 Answers6