correlated queries - 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

SQL Server 2000 Forums

Transact-SQL (2000)

correlated queries

Author

Topic

khurrat
Starting Member

6 Posts

Posted - 2006-11-10 : 04:01:43

i have a table--- dept with 3 columns..
empid,deptid,sal
1 11 111
2 11 222
3 11 333
4 22 100
5 22 200
6 22 300
7 33 1000
8 33 2000
9 33 3000

i want to select the employee with second highest salary in EACH GROUP!!..
could some one help me out with this one...
should not use a cursor...only select statements

SwePeso
Patron Saint of Lost Yaks

30421 Posts

Posted - 2006-11-10 : 04:14:49

You mean for each department?

-- prepare test data
declare @test table (empid tinyint, deptid tinyint, sal smallint)

insert	@test
select	1, 11,  111 union all
select	2, 11,  222 union all
select	3, 11,  333 union all
select	4, 22,  100 union all
select	5, 22,  200 union all
select	6, 22,  300 union all
select	7, 33, 1000 union all
select	8, 33, 2000 union all
select	9, 33, 3000

-- perform the magic
select		t.*
from		@test t
inner join	(
			select		deptid,
					max(sal) ms
			from		@test
			group by	deptid
		) q on q.deptid = t.deptid and q.ms = t.sal
order by	t.sal desc

Why are you asking us to do your homework for you?

Peter Larsson
Helsingborg, Sweden

khurrat
Starting Member

6 Posts

Posted - 2006-11-10 : 04:44:40

Thanks for ur response peter,
there are many ways of getting a max sal for each department.
BUT I am interested in getting second highest, the result should be this:
2 11 222
5 22 200
8 33 2000

This must be simple query for you, but m in a learning phase.

I have a query which gives second highest value:

how do i use this to get the same for each deptid

select max(sal)
from dept
where sal not in (select top 1 sal from dept order by sal desc)

Thank you.

SwePeso
Patron Saint of Lost Yaks

30421 Posts

Posted - 2006-11-10 : 05:02:08

Try this

-- prepare test data
declare @test table (empid tinyint, deptid tinyint, sal smallint)

insert	@test
select	1, 11,  111 union all
select	2, 11,  222 union all
select	3, 11,  333 union all
select	4, 22,  100 union all
select	5, 22,  200 union all
select	6, 22,  300 union all
select	7, 33, 1000 union all
select	8, 33, 2000 union all
select	9, 33, 3000

-- perform the magic
select		t.*
from		@test t
inner join	(
			select		deptid,
					sum(sal) - max(sal) - min(sal) ms
			from		@test
			group by	deptid
		) q on q.deptid = t.deptid and q.ms = t.sal
order by	t.sal desc

since it obviously is either an interview question or homework.

Peter Larsson
Helsingborg, Sweden

khurrat
Starting Member

6 Posts

Posted - 2006-11-10 : 05:16:46

cool!!...
but , what if I have many rows for each group??, not jus 3 .

SwePeso
Patron Saint of Lost Yaks

30421 Posts

Posted - 2006-11-10 : 05:28:16

Yikes.

-- prepare test data
declare @test table (empid tinyint, deptid tinyint, sal smallint)

insert	@test
select	1, 11,  111 union all
select	2, 11,  222 union all
select	3, 11,  333 union all
select	4, 22,  100 union all
select	5, 22,  200 union all
select	6, 22,  300 union all
select	7, 33, 1000 union all
select	8, 33, 2000 union all
select	9, 33, 3000

-- perform the magic
select		t.*
from		(
			select		deptid,
					max(sal) ms
			from		@test
			group by	deptid
		) w			
inner join	@test t on t.deptid = w.deptid and t.sal = (select max(t.sal) from @test t where t.deptid = w.deptid and t.sal < w.ms)

Peter Larsson
Helsingborg, Sweden

khurrat
Starting Member

6 Posts

Posted - 2006-11-10 : 05:39:04

wow!...
it works!!..
thanks for this peter..
I will definately work hard to write such queries n think in this way..

can I ask a question??... if this is allowed here
How long have been into this stream(sql server) ??..

Thanks again!!..

SwePeso
Patron Saint of Lost Yaks

30421 Posts

Posted - 2006-11-10 : 05:43:16

I have worked with SQL Server since early 1994.

Peter Larsson
Helsingborg, Sweden

khurrat
Starting Member

6 Posts

Posted - 2006-11-10 : 05:52:03

GOSH!!..
Thats why my question irritated you ,rt??
nways , thanks for being an inspiration.

SwePeso
Patron Saint of Lost Yaks

30421 Posts

Posted - 2006-11-10 : 06:13:32

No, I was irritated by you not doing your homework.
Or, if it is a interview question, you are cheating yourself by getting that position with not enough skills.

Peter Larsson
Helsingborg, Sweden

khurrat
Starting Member

6 Posts

Posted - 2006-11-10 : 06:34:30

I m not into this stream...I m tryin to learn sql server in my leisure time..N struggled through this one tryin to explore few concepts..using subqueries.... but should have tried a lil more before posting.. I ll do that next time...

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources