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.
Author |
Topic |
gagani
Posting Yak Master
112 Posts |
Posted - 2012-09-24 : 04:19:55
|
1) I need a query which result in the output of second maximum salary in an employee table.2) in which year the maximum number of employees hiredemployeeid employeename salary hiredate1 A 1000 01/02/20002 B 2000 02/02/20043 C 3000 05/09/2008........... |
|
bandi
Master Smack Fu Yak Hacker
2242 Posts |
Posted - 2012-09-24 : 05:41:17
|
1) SELECT MAX(salary) '2nd max sal' FROM YourTable WHERE salary < (SELECT MAX(salary) FROM YourTable)2) SELECT TOP 1 datepart(yy, hire_date) Year, count(employee_id) cnt FROM YourTable GROUP BY datepart(yy, hire_date) ORDER BY cnt DESC--Chandu |
|
|
visakh16
Very Important crosS Applying yaK Herder
52326 Posts |
Posted - 2012-09-24 : 13:17:11
|
[code]Alternate approach for 1SELECT employeeid,employeename,salary,hiredate(SELECT DENSE_RANK() OVER (ORDER BY Salary DESC) AS Rnk,*FROM table)tWHERE Rnk=2modified solution for 2 to take care of cases where there are ties ie multiple years with same maximum employee countSELECT TOP 1 WITH TIES datepart(yy, hire_date) Year, count(employee_id) cntFROM YourTableGROUP BY datepart(yy, hire_date) ORDER BY cnt DESC[/code]------------------------------------------------------------------------------------------------------SQL Server MVPhttp://visakhm.blogspot.com/ |
|
|
|
|
|