Select last two row deptment wise

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 last two row deptment wise

Author

Topic

Prabu.Ramaiah
Starting Member

15 Posts

Posted - 2010-05-24 : 01:22:15

Hi

I am having the below

Empid Deptid Salary
----- ------- ------
1 10 100
2 20 200
3 30 300
4 40 400
5 10 100
6 20 200
7 30 300
8 40 400
9 50 500
10 10 100
11 20 200
12 30 300
--------------------------

I want to select the last two rows in dept wise like this

Empid Deptid Salary
----- ------- ------
3 30 300
4 40 400
8 40 400
9 50 500
11 20 200
12 30 300
--------------------------

How to select please help me

karthik_padbanaban
Constraint Violating Yak Guru

263 Posts

Posted - 2010-05-24 : 02:27:17

Did you mean for each department you want last two records.

Karthik
http://karthik4identity.blogspot.com/

Prabu.Ramaiah
Starting Member

15 Posts

Posted - 2010-05-24 : 02:29:18

quote:
Originally posted by karthik_padbanaban

Did you mean for each department you want last two records.

Karthik
http://karthik4identity.blogspot.com/

Yes

karthik_padbanaban
Constraint Violating Yak Guru

263 Posts

Posted - 2010-05-24 : 02:31:33

Is this the one you want......?

declare @a table (Empid int, Deptid int ,Salary int )
insert into @a values (1, 10, 100)
insert into @a values (2, 20, 200)
insert into @a values (3, 30, 300)
insert into @a values (4, 40, 400)
insert into @a values (5, 10, 100)
insert into @a values (6, 20, 200)
insert into @a values (7, 30, 300)
insert into @a values (8, 40, 400)
insert into @a values (9, 50, 500)
insert into @a values (10, 10, 100)
insert into @a values (11, 20, 200)
insert into @a values (12, 30, 300)

select empid,deptid,salary from (
select empid,deptid,salary,row_number()over(partition by deptid order by empid desc) as row from @a) a
where row <=2


empid       deptid      salary
----------- ----------- -----------
10          10          100
5           10          100
11          20          200
6           20          200
12          30          300
7           30          300
8           40          400
4           40          400
9           50          500

Karthik
http://karthik4identity.blogspot.com/

Prabu.Ramaiah
Starting Member

15 Posts

Posted - 2010-05-24 : 02:39:38

No, I want the below output, actually the deptid 10-40 one department, 10-50 another dept, 10-30 another deptment

Empid Deptid Salary
----- ------- ------
3 30 300
4 40 400
8 40 400
9 50 500
11 20 200
12 30 300
--------------------------

kashyapsid
Yak Posting Veteran

78 Posts

Posted - 2010-05-24 : 03:26:09

we can't understand wt you want the output?????

KaShYaP

Prabu.Ramaiah
Starting Member

15 Posts

Posted - 2010-05-24 : 03:29:36

pk_bohra
Master Smack Fu Yak Hacker

1182 Posts

Posted - 2010-05-24 : 04:29:51

quote:
Originally posted by Prabu.Ramaiah

Hi

I am having the below

Empid Deptid Salary
----- ------- ------
1 10 100
2 20 200
3 30 300
4 40 400
5 10 100
6 20 200
7 30 300
8 40 400
9 50 500
10 10 100
11 20 200
12 30 300
--------------------------

I want to select the last two rows in dept wise like this

Empid Deptid Salary
----- ------- ------
3 30 300
4 40 400
8 40 400
9 50 500
11 20 200
12 30 300
--------------------------

How to select please help me

For dept 30 you have empid 3,7 & 12 and all have same salary. On what basis you are selecting 3 & 12 ?

naveengopinathasari
Yak Posting Veteran

60 Posts

Posted - 2010-05-24 : 05:47:07

Hi,

Please Execute this query and check the Result.
Create a Table With the Name EmpSal give the same column names as you have given
Query to Fecth the result as you have mentioned

as mentioned by you your result set doesnt match the logic you have given.
as i understand you require to list dept wise last 2 rows (each dept last 2 rows).
If so this query will return.

----------------------------------------------------------------------------------------------
Select *
FROM
(Select ES.EmpID , ES.DeptID , ES.Salary , EP.Cnt ,
NTILE(10) OVER(PARTITION BY ES.DeptID ORDER BY ES.DeptID DESC) AS RankRows
FROM EmpSal ES,
(Select DeptID , Count(DeptID) AS Cnt FROM EmpSal GROUP BY DeptID ) EP
WHERE ES.DeptID = EP.DeptID
GROUP BY ES.EmpID , ES.DeptID , ES.Salary ,EP.Cnt
) FinalQry
WHERE FinalQry.RankRows >FinalQry.Cnt-2
----------------------------------------------------------------------------------------------

Query to Create the Table
CREATE TABLE [dbo].[EmpSal](
[EmpID] [int] NULL,
[DeptID] [int] NULL,
[Salary] [int] NULL
) ON [PRIMARY]

Lets unLearn

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources