Get max of two entries query

Question

Query:

select machinename, StatusCode, size
from machine where MachineID In( '33','22') and StatusCode = 166 
ORDER BY size DESC

Result:

machinename  StatusCode  size
-----------  ----------  ----
test1        166         50
test1        166         25
test2        166         75
test2        166         48

Requirement:

I need to display only one entry for each machine. I have to do this by taking the max size value between the two entries as shown above. like for test1 i have two sizes 50 and 25 I have to show the row which has 50 and ignore row which has 25.

Thanks

Desired Result:

machinename  StatusCode  size
-----------  ----------  ----
test1        166         50
test2        166         75

What exactly are you trying to accomplish? I don't understand what you mean by "where the size is greater than the other." — user569322
– user569322, Commented Sep 28, 2011 at 23:36
I don't understand what you mean by where the size is greater than the other. Are you just looking for the max size for that machine name? — Brian Hoover
– Brian Hoover, Commented Sep 28, 2011 at 23:37
eg for machine test1, i have two sizes 50 and 25 so i want to display only the row with size 50 and not 25. let me know if i need to explain mare — user175084
– user175084, Commented Sep 28, 2011 at 23:41

Brian Hoover · Accepted Answer · 2011-09-29 15:16:26Z

4

This will work, but you won't be able to order by starttime

select machinename, StatusCode, max(size) as size
from machine where MachineID In( '33','22') and StatusCode = 166 
group by machinename, StatusCode
order by max(size) DESC

edited Sep 29, 2011 at 15:16

answered Sep 28, 2011 at 23:43

Brian Hoover

7,9652 gold badges31 silver badges41 bronze badges

Sign up to request clarification or add additional context in comments.

3 Comments

Mark Byers Over a year ago

+1 This is best for updated query. Except you have missed a DESC.

Andriy M Over a year ago

Actually it would be possible to ORDER BY MAX(StartTime).

Brian Hoover Over a year ago

Yes, but that wouldn't tie to the same record that had the max(size), so all it would really tell you is when the last time this machine had a test

Mark Byers · Accepted Answer · 2011-09-29 00:06:42Z

3

If you wanted to order by StartTime you would have to use ROW_NUMBER so that you could select the starttime field:

SELECT machinename, StatusCode, size
FROM (
    SELECT
        machinename,
        StatusCode,
        StartTime
        size,
        ROW_NUMBER() OVER (PARTITION BY MachineID ORDER BY size DESC) AS rn
    FROM machine
    WHERE MachineID IN ('33','22')
    AND StatusCode = 166 
) T1
WHERE rn = 1
ORDER BY StartTime DESC

But if you want to order by size, it's easier:

SELECT machinename, StatusCode, MAX(size) AS size
FROM machine
WHERE MachineID IN ('33','22') AND StatusCode = 166 
GROUP BY MachineID
ORDER BY MAX(size) DESC

edited Sep 29, 2011 at 0:06

answered Sep 28, 2011 at 23:42

Mark Byers

843k202 gold badges1.6k silver badges1.5k bronze badges

1 Comment

user175084 Over a year ago

sorry but the server went down. will test and check correct answer

billinkc · Accepted Answer · 2011-09-28 23:43:21Z

2

Use a windowing function to generate ids to partition on your key and then order by largest to smallest. This will generate 1-N for each partition where 1 will correspond to the largest value from the ORDER BY. We then filter for the largest one.

; WITH MACHINES AS
(
select machinename, StatusCode, size
, ROW_NUMBER() OVER (PARTITION BY machinename ORDER BY size DESC) AS RN
from machine where MachineID In( '33','22') and StatusCode = 166 

)
SELECT
select machinename, StatusCode, size
FROM MACHINES M
WHERE M.rn = 1;

answered Sep 28, 2011 at 23:43

billinkc

61.4k10 gold badges110 silver badges169 bronze badges

Comments

billinkc · Accepted Answer · 2011-09-28 23:52:27Z

2

select machinename, StatusCode, size
from machine a
where MachineID In( '33','22') and StatusCode = 166 
and size = (select max(size) 
from machine b
where MachineID In( '33','22') and StatusCode = 166 
and a.machinename = b.machinename)
ORDER BY StartTime DESC

HTH.

edited Sep 28, 2011 at 23:52

billinkc

61.4k10 gold badges110 silver badges169 bronze badges

answered Sep 28, 2011 at 23:45

Thinhbk

2,2221 gold badge25 silver badges34 bronze badges

1 Comment

user175084 Over a year ago

sorry but the server went down. will test and check correct answer

Collectives™ on Stack Overflow

Get max of two entries query

4 Answers 4

3 Comments

1 Comment

Comments

1 Comment

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

4 Answers 4

3 Comments

1 Comment

Comments

1 Comment

Your Answer

Sign up or log in

Post as a guest

Related