select smaller value - SQL Server Forums

Please start any new threads on our new site at https://forums.sqlteam.com. We've got lots of great SQL Server experts to answer whatever question you can come up with.

All Forums

General SQL Server Forums

New to SQL Server Programming

select smaller value

Author

Topic

mavericky
Posting Yak Master

117 Posts

Posted - 2011-09-28 : 18:57:19

Hi All,
I get a result after execution of my query:

ID Value
5 200
6 191

I want to write a general query to select the ID corresponding to lesser value. Can anybody please help me?

Thanks,
Mavericky

mavericky
Posting Yak Master

117 Posts

Posted - 2011-09-28 : 18:57:59

Table Name is Emp.
Fields are ID and Value.

sunitabeck
Master Smack Fu Yak Hacker

5155 Posts

Posted - 2011-09-28 : 19:08:20

You can use the min function like this:


select TOP 1 ID from Emp group by ID order by MIN(value);

If you are on SQL 2005 or higher, it may be more efficient to use row_number function like this:

with cte as
( select *, ROW_NUMBER() OVER (ORDER BY value) RN from Emp )
select ID from cte where RN = 1

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources