I have a sample employee table with distinct departments now the scenario is need to display the highest repeated departments in a employee table with all columns

Question

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 3-Mar-19 20:38pm

Member 13932549

Updated 5-Mar-19 6:19am

Add a Solution

2 solutions

Add a Solution

Add your solution here

Treat my content as plain text, not as HTML

Preview 0

…

Existing Members

Sign in to your account

...or Join us

Download, Vote, Comment, Publish.

Your Email
Password
Forgot your password?

Your Email
This email is in use. Do you need your password?
Optional Password

I have read and agree to the Terms of Service and Privacy Policy
Please subscribe me to the CodeProject newsletters

When answering a question please:

Read the question carefully.
Understand that English isn't everyone's first language so be lenient of bad spelling and grammar.
If a question is poorly phrased then either ask for clarification, ignore it, or edit the question and fix the problem. Insults are not welcome.
Don't tell someone to read the manual. Chances are they have and don't get it. Provide an answer or move on to the next question.

Let's work to help developers, not make them feel stupid.

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

OriginalGriff · Answer 1 · 2019-03-03T20:53:00

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.

Richard Deeming · Answer 2 · 2019-03-05T06:20:00

Solution 2

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
;

Posted 5-Mar-19 6:20am

Richard Deeming