Click here to Skip to main content
15,311,561 members
Please Sign up or sign in to vote.
0.00/5 (No votes)
See more:
-->INPUT:
empid department  names    salary
1	dotnet	    klrahul	     1000
2	dotnet	    dhawan	     2000
3	dotnet	    virat	     1500
4	design	    dhoni	     3000
5	design	    karthik	     2000
6	design	    phant	     3040
7	php	        chawal	     4000
8	php	        kpandya	     2000
9	php	        skaual	     4300
10	php	        bhumra	     2000
11	dotnet	    vijay	     1646

-->OUTPUT:
empid   department  names   salary
1	dotnet	    klrahul	     1000
2	dotnet	    dhawan	     2000
3	dotnet	    virat	     1500
11	dotnet	    vijay	     1646
7	php	        chawal	     4000
8	php	        kpandya	     2000
9	php	        skaual	     4300
10	php	        bhumra	     2000


What I have tried:

I tried group by having clause but i am unable to fetch all the columns can anyone help me with a SQL Server 2012 query.
Posted
Updated 5-Mar-19 6:19am

Group by isn't for "all records" - it's an aggregation function.
See here: SQL GROUP By and the "Column 'name' is invalid in the select list because..." error[^] and it explains it.
Probably, you'll have to use a GROUP BY with aggregation functions like COUNT and MAX together with a JOIN to select the records you want.
Start by writing the GROUP by to return the department names you need - that's pretty simple - then add that to a JOIN to fetch the rows you want.
   
Assuming Microsoft SQL Server, something like this should work:
SQL
SELECT TOP 1 WITH TIES
    empid,
    department,
    names,
    salary
FROM
    YourTable
ORDER BY
    COUNT(1) OVER (PARTITION BY department) DESC
;
   

This content, along with any associated source code and files, is licensed under The Code Project Open License (CPOL)



CodeProject, 20 Bay Street, 11th Floor Toronto, Ontario, Canada M5J 2N8 +1 (416) 849-8900