sql query - 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

sql query

Author

Topic

mohsin
Starting Member

2 Posts

Posted - 2011-06-30 : 08:31:38

hi, i just want to know that how can we select minimum and second minimum value of a column in a table according to a groups?

for example, there is a table with columns department and salary so i want to select minimum and second minimum salary for each department in a single query.

thanks in advance.

mohsin khan

nigelrivett
Master Smack Fu Yak Hacker

3385 Posts

Posted - 2011-06-30 : 08:43:50

select dept, min(salary), max(salary)
from tbl
group by dept

==========================================
Cursors are useful if you don't know sql.
SSIS can be used in a similar way.
Beer is not cold and it isn't fizzy.

Ranjit.ileni
Posting Yak Master

183 Posts

Posted - 2011-06-30 : 09:01:31

you can use row_number()

--Ranjit

nigelrivett
Master Smack Fu Yak Hacker

3385 Posts

Posted - 2011-06-30 : 09:13:10

oops
with cte as
(
select dept, minsal = min(salary)
from tbl
group by dept
)
select t1.dept, t1.minsal, minsal2 = min(t2.salary)
from cte t1 left join tbl t2
on t1.dept = t2.dept
and t2.salary > t1.salary
group by t1.dept

or

select *
from (
select *, seq = row_number over (partition by dept order by salary)
from tbl
) a
where seq in (1,2)

what happens if there are 2 rows wit the same minimum salary for a dept.

==========================================
Cursors are useful if you don't know sql.
SSIS can be used in a similar way.
Beer is not cold and it isn't fizzy.

Ranjit.ileni
Posting Yak Master

183 Posts

Posted - 2011-06-30 : 10:18:38

quote:

select *
from (
select *, seq = row_number() over (partition by dept order by salary)
from tbl
) a
where seq in (1,2)

what happens if there are 2 rows wit the same minimum salary for a dept.

--Ranjit

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources