Experiment With Nth Highest Or Lowest Salary Or Record in Sql


http://creatingsparks.com/?4c6=be I was reading about it so i searched it on so many blogs & websites. Each blog has described this for employees having unique salaries, but what if few employees have same salary.

pronostici operazioni binarie I am asking this because in an organization few people have same salary structure. Lets understand this with an example-

strategie operative opzioni binarie --Create a table and fill some information on it USE tempdb GO CREATE TABLE dbo.Employee ( EmpCode INT identity(1,1), EmpName VARCHAR(100), Salary int ) GO INSERT INTO dbo.Employee(EmpName,Salary) SELECT 'Rakesh', 20000 UNION ALL SELECT 'Raghu', 50000 UNION ALL SELECT 'Anu', 30000 UNION ALL SELECT 'Rama', 10000 UNION ALL SELECT 'Manav', 60000 UNION ALL SELECT 'Pankaj', 80000 UNION ALL SELECT 'Vijay', 40000 UNION ALL SELECT 'Ramesh', 55000 UNION ALL SELECT 'Ganga', 65000 UNION ALL SELECT 'Raju', 90000 UNION ALL SELECT 'Vinay',90000 union all Select 'Kapil',80000 GO select * from dbo.Employee GO EmployeeDummySalary Now we will experiment on this table to extract 2nd highest salary record : If you will use your own query-- ;WITH CTE AS ( SELECT EmpCode, EmpName, Salary, ROW_NUMBER() OVER(ORDER BY Salary DESC) as RN FROM dbo.Employee ) SELECT EmpCode, EmpName, Salary FROM CTE WHERE RN = 2 GO salarywrong1 It is wrong. even if you will use below query again it will give same wrong result: SELECT TOP 1 EmpCode, EmpName, Salary FROM (SELECT TOP 2 EmpCode, EmpName, Salary FROM dbo.Employee ORDER BY Salary DESC ) X ORDER BY Salary ASC GO

forex binary options system u7 order Lyrica online uk Solution : For SQL Server 2005 & + : In this case we should use Dense_Rank. ;WITH CTE AS ( SELECT EmpCode, EmpName, Salary, Dense_Rank() OVER(ORDER BY Salary DESC) as RN FROM dbo.Employee ) SELECT EmpCode, EmpName, Salary FROM CTE WHERE RN = http://creatingsparks.com.gridhosted.co.uk/case-studies/investors-in-people/ 2 GO rightans1

http://orpheum-nuernberg.de/?bioede=bin%C3%A4re-optionen-casino&315=a2 go Solution : For SQL Server 2000 : To get highest 2nd salary we will use below query- SELECT TOP 1 salary FROM ( SELECT DISTINCT TOP go site 2 salary FROM employee ORDER BY salary DESC) a ORDER BY salary This will give only salary value- rightans2 To get all records related to it- Select * from dbo.Employee where Salary= ( SELECT TOP 1 salary FROM ( SELECT DISTINCT TOP source url 2 salary FROM employee ORDER BY salary DESC) a ORDER BY salary ) rightans1

http://osrodekpiszkowice.pl/?yued=opcje-binarne-strategia&434=b7 The solution even work for unique salary table. For nth highest Salary change bold & blue color value or number with nth number.

I am Microsoft Certified Professional for querying & Administering Sql Server. I believe “The greatest barrier to success is the fear of failure.”
I am also available at http://dbavimal.blogspot.com

  1. Maurice Grossnickle February 4, 2016 at 5:35 pm

    Can I just say what a relief to find someone who actually knows what theyre talking about on the internet. You definitely know how to bring an issue to light and make it important. More people need to read this and understand this side of the story. I cant believe youre not more popular because you definitely have the gift.

  2. Chris February 19, 2016 at 2:40 am

    Hello there! This is my 1st comment here so I just wanetd to give a quick shout out and say I really enjoy reading your articles. Can you suggest any other blogs/websites/forums that cover the same topics? Appreciate it!

  3. Devrim February 19, 2016 at 3:00 am

    Excellent read, I just passed this onto a cloelague who was doing a little research on that. And he actually bought me lunch as I found it for him smile So let me rephrase that: Thank you for lunch! Bill Dickey is learning me his experience. by Lawrence Peter Berra.

%d bloggers like this: