Get the second smallest 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

Get the second smallest value

Author

Topic

cloud23
Starting Member

8 Posts

Posted - 2007-05-20 : 07:08:26

I have met up a problem. How to i get the second smallest value instead of the smallest value in a table using min(). Any other function rather than min()?

cloud23
Starting Member

8 Posts

Posted - 2007-05-20 : 07:11:46

For example (1,2,4,6,7)

I want to get the value 2 instead of 1. Any function to do that?

cloud23
Starting Member

8 Posts

Posted - 2007-05-20 : 07:19:09

For example (1,2,4,6,7)

I want to get the value 2 instead of 1. Any function to do that?

subrata4allfriends
Starting Member

24 Posts

Posted - 2007-05-20 : 07:39:58

Hi,

I think you need the second lowest value from a column.
Please try this query, hope this will solve your problem.

SELECT MIN(COL1) FROM TESTTABLE
WHERE COL1 <> (SELECT MIN(COL1) FROM TESTTABLE)

spirit1
Cybernetic Yak Master

11752 Posts

Posted - 2007-05-20 : 10:31:11

better apporach would be this:

select top 1 yourColumn
from
(
select top 2 yourColumn
from YourTable
order by yourColumn asc
) t
order by yourColumn desc

_______________________________________________
Causing trouble since 1980
blog: http://weblogs.sqlteam.com/mladenp

cloud23
Starting Member

8 Posts

Posted - 2007-05-20 : 23:10:18

Thanks for the help above.
But how do i go through
(1,1,1,1,2,4,6,8,8,9)

I mean getting value from 1 then 1 again then followed by 1,1,2,4,6,8,8,9.

khtan
In (Som, Ni, Yak)

17689 Posts

Posted - 2007-05-20 : 23:11:57

are you using SQL Server 2005 ? you can use the row_number() function

cloud23
Starting Member

8 Posts

Posted - 2007-05-20 : 23:12:35

TOP command doesnt register? hmm i am using sql 9version.

khtan
In (Som, Ni, Yak)

17689 Posts

Posted - 2007-05-20 : 23:19:01

yes. TOP command will works as what spirit1 as shown

See here


declare @tbl table
(
	col	int
)

insert into @tbl
select	1 union all
select	1 union all
select	1 union all
select	1 union all
select	2 union all
select	4 union all
select	6 union all
select	8 union all
select	8 union all
select	9

select	top 1 col
from
(
	select	top 2 col
	from	@tbl
	order by col asc
) a
order by col desc

select	col
from
(
	select	col, row_no = row_number() over (order by col)
	from	@tbl
) a
where	row_no	= 2

madhivanan
Premature Yak Congratulator

22864 Posts

Posted - 2007-05-21 : 00:08:10

My favourite one


select	max(col) as col
from
(
	select	top 2 col
	from	@tbl
	order by col asc
) a

Madhivanan

Failing to plan is Planning to fail

madhivanan
Premature Yak Congratulator

22864 Posts

Posted - 2007-05-21 : 00:12:09

quote:
Originally posted by cloud23

Thanks for the help above.
But how do i go through
(1,1,1,1,2,4,6,8,8,9)

I mean getting value from 1 then 1 again then followed by 1,1,2,4,6,8,8,9.

What is your expected result?
If you want to get 2, then use DISTINCT

Madhivanan

Failing to plan is Planning to fail

spirit1
Cybernetic Yak Master

11752 Posts

Posted - 2007-05-21 : 03:52:39

first you have to specify how your data is saved.
is each value in one row and all are in one column or
are all values in one row just separated with commas?

_______________________________________________
Causing trouble since 1980
blog: http://weblogs.sqlteam.com/mladenp

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources