SQLServerCentral / SQL Server 2008 / T-SQL (SS2K8) / T-SQL, SELECT 10TH AND 11TH HIGHEST SALARY EMPLOYEES FROM EMPLOYEE TABLE / Latest PostsInstantForum.NET v2.9.0SQLServerCentralhttp://www.sqlservercentral.com/Forums/notifications@sqlservercentral.comSun, 19 May 2013 16:26:54 GMT20RE: T-SQL, SELECT 10TH AND 11TH HIGHEST SALARY EMPLOYEES FROM EMPLOYEE TABLEhttp://www.sqlservercentral.com/Forums/Topic1412625-392-1.aspx[quote][b]Lynn Pettis (1/28/2013)[/b][hr]Hoping this is late enough not to be useful with your interview but soon enough to help you learn something[/quote]Why does this bring to mind the vision of an interviewee tapping away at an iPad during the interview, posting a question to SSC, in the hopes that it will be answered by the time the interviewer blinks twice?Tue, 29 Jan 2013 00:47:55 GMTdwain.cRE: T-SQL, SELECT 10TH AND 11TH HIGHEST SALARY EMPLOYEES FROM EMPLOYEE TABLEhttp://www.sqlservercentral.com/Forums/Topic1412625-392-1.aspxHoping this is late enough not to be useful with your interview but soon enough to help you learn something:[code="sql"]WITH Salaries AS (SELECT EmpID, EmpLastName, EmpFirstName, Salary, rn = ROW_NUMBER() OVER (PARTITION BY EmpID ORDER BY Salary DESC) FROM dbo.Employee)SELECT *FROM SalariesWHERE rn between 10 and 11;-- or, without windowing functions ala SQL Server 2000:select top 2 *from (select top 11 EmpID, EmpLastName, EmpFirstName, Salary from dbo.Employee order by Salary DESC) dtorder by dt.Salary ASC;[/code]Mon, 28 Jan 2013 22:14:45 GMTLynn PettisRE: T-SQL, SELECT 10TH AND 11TH HIGHEST SALARY EMPLOYEES FROM EMPLOYEE TABLEhttp://www.sqlservercentral.com/Forums/Topic1412625-392-1.aspxthis should work too...declare @t table(empname varchar(32),sal money)insert into @t select 'patrick',1000unionselect 'john',12000unionselect 'peter',500unionselect 'robert',360unionselect 'steve',810unionselect 'edward',3000unionselect 'sean',1200unionselect 'ricky',500select * from(select empname,ROW_NUMBER() over (order by sal desc) as position from @t)a where position between 5 and 6Mon, 28 Jan 2013 15:24:16 GMTsqlbi.vvamsiRE: T-SQL, SELECT 10TH AND 11TH HIGHEST SALARY EMPLOYEES FROM EMPLOYEE TABLEhttp://www.sqlservercentral.com/Forums/Topic1412625-392-1.aspxyou can also do it SQL2000 style(before the wonderful row_number/ranking functions came out) , using nested TOP statements to get the top 11, and then the top 2 of that in the opposite order.Mon, 28 Jan 2013 14:00:37 GMTLowellRE: T-SQL, SELECT 10TH AND 11TH HIGHEST SALARY EMPLOYEES FROM EMPLOYEE TABLEhttp://www.sqlservercentral.com/Forums/Topic1412625-392-1.aspxYou may get an error, misplaced parens and the sort is wrong.Mon, 28 Jan 2013 13:58:10 GMTLynn PettisRE: T-SQL, SELECT 10TH AND 11TH HIGHEST SALARY EMPLOYEES FROM EMPLOYEE TABLEhttp://www.sqlservercentral.com/Forums/Topic1412625-392-1.aspxthanks. i will blug in and see if that answers the question Mon, 28 Jan 2013 13:49:47 GMTMassanRE: T-SQL, SELECT 10TH AND 11TH HIGHEST SALARY EMPLOYEES FROM EMPLOYEE TABLEhttp://www.sqlservercentral.com/Forums/Topic1412625-392-1.aspxUsing the Rank function would be very helpfull for this., possibly a cte.;WITH Salaries AS (SELECT EmpID,EmpLastName,EmpFirstName,SalaryRANK() OVER (PARTITION BY EmpID, ORDER BY Salary)) FROM dbo.Employee)AS SalaryRankSELECT *FROM SalariesWHERE (SalaryRank = 10 OR SalaryRank=11)Mon, 28 Jan 2013 13:37:04 GMTRay MRE: T-SQL, SELECT 10TH AND 11TH HIGHEST SALARY EMPLOYEES FROM EMPLOYEE TABLEhttp://www.sqlservercentral.com/Forums/Topic1412625-392-1.aspxHow about thinking you need the first 11 but you need to discard the first 9. How do you discard rows? EXCEPT, NOT IN, NOT EXISTS, OUTER JOIN?Mon, 28 Jan 2013 13:36:51 GMTLuis CazaresT-SQL, SELECT 10TH AND 11TH HIGHEST SALARY EMPLOYEES FROM EMPLOYEE TABLEhttp://www.sqlservercentral.com/Forums/Topic1412625-392-1.aspxPLEASE HELP ME WHO EVER CAN WRITE MORE EFFICIENT T-SQL THAT ANSWERS THE QUESTION.THIS IS WHEN WE DONT KNOW THE SALARY INFORMATION AND WE ONLY CARE THE 10TH AND 11TH EMPLOYEESITS FROM JOB INTERVIEW, WHICH I SIAD ! I DONT KNOW!Mon, 28 Jan 2013 13:26:37 GMTMassan