Link to home
Start Free TrialLog in
Avatar of Sriv
Sriv

asked on

First Max value

I have below scenario and in SQL i want to display ID of first max value. I want to display 5 because it has first max value

ID   Value
1      10
2       20
3       30
4       40
5       50
6       50
7       50
Avatar of Jim Horn
Jim Horn
Flag of United States of America image

Give this a whirl..
SELECT Min(ID)
FROM YourTable 
WHERE Value = (SELECT MAX(Value) as value_max FROM YourTable) 

Open in new window

ASKER CERTIFIED SOLUTION
Avatar of Scott Pletcher
Scott Pletcher
Flag of United States of America image

Link to home
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
Start Free Trial
Test table, solution and the result:
--use ee
declare @FirsMaxtbl table(ID int, [Value] int);

Insert into @FirsMaxtbl(ID, [Value]) Values
(1, 10), (2, 20), (3, 30), (4, 40), (5, 50), (6, 50), (7, 50);

--Solution:
SELECT  top 1 ID, Value as FirstMaxValue
FROM @FirsMaxtbl Order by Value Desc, ID;

--Result:
ID  FirstMaxValue
5	    50

Open in new window