Styjun

Why would an airport be depicted with symbology for runways longer than 8,069 feet even though it is reported on the sectional as 7,200 feet?

How invisible hand adjusts stock prices if company is listed on multiple exchanges, under multiple currencies, and one of the currencies plunges?

Why can't some airports handle heavy aircraft while others do it easily (same runway length)?

Leaving the USA for 10 yrs when you have asylum

Why do the British opposition parties not want a new election?

Distinguishing between octahedral and tetrahedral holes

Contour plot of a sequence of spheres with increasing radius

2 load centers under 1 meter: do you need bonding and main breakers at both?

Are there any space probes or landers which regained communication after being lost?

Are personality traits, ideals, bonds, and flaws required?

What makes things real?

A question regarding Buddhist world view and sense organs and their objects

Is there a way to deal with desistance in a off-chain game?

Is every sentence we write or utter either true or false?

How do you say "to hell with everything" in French?

Supervisor wants me to support a diploma-thesis software tool after I graduated

Bit floating sequence

How can I return only the number of paired values in array?

Capacitors with same voltage, same capacitance, same temp, different diameter?

Friend is very nitpicky about side comments I don't intend to be taken too seriously

Strategies for dealing with chess burnout?

Why did Tony's Arc Reactor do this?

Why is it that I have to play this note on the piano as A sharp?

Contractor cut joist hangers to make them fit

How to get eid from table which is having max number of count in the Mid?

How do I get list of all tables in a database using TSQL?Select columns from result set of stored procedureHow can I get column names from a table in SQL Server?How to 'insert if not exists' in MySQL?Get all table names of a particular database by SQL query?Get top 1 row of each groupGet other user_ids which have same midGet Count and Unique Values from one tableHow to get depatment wise max salary as well as name of employee having it?How to Find Row with MAX value in another table

.everyoneloves__top-leaderboard:empty,.everyoneloves__mid-leaderboard:empty,.everyoneloves__bot-mid-leaderboard:empty margin-bottom:0;

-3

I have a table employee which is having following structure:

Id. Mid Salary

1 20 200
2 20 3000
3 30 200
4 34 4000
5 30 300
6 30 400
1 23 440
1 24 333
2 21 3

I want to get result like:

Id Mid
1 3

sql sql-server

share|improve this question

asked Mar 28 at 7:28

mahadev dhyanimahadev dhyani

1755 bronze badges

• 
  
  
  

  
  

  
  
  
  
  
  

  
  
  So you want the most frequent id and the number of times it appears in the table?
  
  – Zohar Peled
  Mar 28 at 7:30
  

  
  
  
  


• 
  
  
  

  
  

  
  
  
  
  
  

  
  
  Can you explain why 3?
  
  – Prashant Pimpale
  Mar 28 at 7:30
  

  
  
  
  


• 
  
  
  

  
  1
  

  
  
  
  
  
  

  
  
  The question doesn't appear to include any attempt at all to solve the problem. StackOverflow expects you to try to solve your own problem first, as your attempts help us to better understand what you want. Please edit the question to show what you've tried, and show a specific roadblock you're running into with Minimal, Complete, and Verifiable example. For more information, please see How to Ask.
  
  – Andreas
  Mar 28 at 7:31
  

  
  
  
  


• 
  
  
  

  
  

  
  
  
  
  
  

  
  
  3 will be the count of mid corresponding to id 1.
  
  – mahadev dhyani
  Mar 28 at 7:32
  

  
  
  
  


• 
  
  
  

  
  

  
  
  
  
  
  

  
  
  #zohar peled yes you are right?
  
  – mahadev dhyani
  Mar 28 at 7:34
  

  
  
  
  

add a comment | 

-3

I have a table employee which is having following structure:

Id. Mid Salary

1 20 200
2 20 3000
3 30 200
4 34 4000
5 30 300
6 30 400
1 23 440
1 24 333
2 21 3

I want to get result like:

Id Mid
1 3

sql sql-server

share|improve this question

asked Mar 28 at 7:28

mahadev dhyanimahadev dhyani

1755 bronze badges

• 
  
  
  

  
  

  
  
  
  
  
  

  
  
  So you want the most frequent id and the number of times it appears in the table?
  
  – Zohar Peled
  Mar 28 at 7:30
  

  
  
  
  


• 
  
  
  

  
  

  
  
  
  
  
  

  
  
  Can you explain why 3?
  
  – Prashant Pimpale
  Mar 28 at 7:30
  

  
  
  
  


• 
  
  
  

  
  1
  

  
  
  
  
  
  

  
  
  The question doesn't appear to include any attempt at all to solve the problem. StackOverflow expects you to try to solve your own problem first, as your attempts help us to better understand what you want. Please edit the question to show what you've tried, and show a specific roadblock you're running into with Minimal, Complete, and Verifiable example. For more information, please see How to Ask.
  
  – Andreas
  Mar 28 at 7:31
  

  
  
  
  


• 
  
  
  

  
  

  
  
  
  
  
  

  
  
  3 will be the count of mid corresponding to id 1.
  
  – mahadev dhyani
  Mar 28 at 7:32
  

  
  
  
  


• 
  
  
  

  
  

  
  
  
  
  
  

  
  
  #zohar peled yes you are right?
  
  – mahadev dhyani
  Mar 28 at 7:34
  

  
  
  
  

add a comment | 

I have a table employee which is having following structure:

Id. Mid Salary

1 20 200
2 20 3000
3 30 200
4 34 4000
5 30 300
6 30 400
1 23 440
1 24 333
2 21 3

I want to get result like:

Id Mid
1 3

sql sql-server

share|improve this question

asked Mar 28 at 7:28

mahadev dhyanimahadev dhyani

1755 bronze badges

Id. Mid Salary

1 20 200
2 20 3000
3 30 200
4 34 4000
5 30 300
6 30 400
1 23 440
1 24 333
2 21 3

Id Mid
1 3

sql sql-server

sql sql-server

share|improve this question

asked Mar 28 at 7:28

mahadev dhyanimahadev dhyani

1755 bronze badges

share|improve this question

asked Mar 28 at 7:28

mahadev dhyanimahadev dhyani

1755 bronze badges

asked Mar 28 at 7:28

mahadev dhyanimahadev dhyani

1755 bronze badges

asked Mar 28 at 7:28

mahadev dhyanimahadev dhyani

1755 bronze badges

2 Answers
2

active

oldest

votes

1

You can try below - using top with ties and aggregation

select top 1 with ties id, count(mid) as Mid
from table
group by id
order by count(mid) desc

share|improve this answer

answered Mar 28 at 7:38

fa06fa06

24.9k55 gold badges1111 silver badges2020 bronze badges

add a comment | 

1

use top 1

select top 1 id,count(*) as cnt
from table 
group by id
order by cnt desc

if you need ties then use with ties

select top 1 with ties id,count(*) as cnt
 from table 
 group by id
 order by cnt desc

share|improve this answer

edited Mar 28 at 7:45

answered Mar 28 at 7:37

Zaynul Abadin TuhinZaynul Abadin Tuhin

23.7k44 gold badges1616 silver badges4141 bronze badges

• 
  
  
  

  
  

  
  
  
  
  
  

  
  
  If multiple ids have the same count in that case top 1 will display 1 id only
  
  – mahadev dhyani
  Mar 28 at 7:43
  

  
  
  
  


• 
  
  
  

  
  

  
  
  
  
  
  

  
  
  @mahadevdhyani yes it will show 1 id but if you need all then you have to use ties
  
  – Zaynul Abadin Tuhin
  Mar 28 at 7:44
  

  
  
  
  

add a comment | 

Your Answer

StackExchange.ifUsing("editor", function ()
StackExchange.using("externalEditor", function ()
StackExchange.using("snippets", function ()
StackExchange.snippets.init();
);
);
, "code-snippets");

StackExchange.ready(function()
var channelOptions =
tags: "".split(" "),
id: "1"
;
initTagRenderer("".split(" "), "".split(" "), channelOptions);

StackExchange.using("externalEditor", function()
// Have to fire editor after snippets, if snippets enabled
if (StackExchange.settings.snippets.snippetsEnabled)
StackExchange.using("snippets", function()
createEditor();
);

else
createEditor();

);

function createEditor()
StackExchange.prepareEditor(
heartbeatType: 'answer',
autoActivateHeartbeat: false,
convertImagesToLinks: true,
noModals: true,
showLowRepImageUploadWarning: true,
reputationToPostImages: 10,
bindNavPrevention: true,
postfix: "",
imageUploader:
brandingHtml: "Powered by u003ca class="icon-imgur-white" href="https://imgur.com/"u003eu003c/au003e",
contentPolicyHtml: "User contributions licensed under u003ca href="https://creativecommons.org/licenses/by-sa/4.0/"u003ecc by-sa 4.0 with attribution requiredu003c/au003e u003ca href="https://stackoverflow.com/legal/content-policy"u003e(content policy)u003c/au003e",
allowUrls: true
,
onDemand: true,
discardSelector: ".discard-answer"
,immediatelyShowMarkdownHelp:true
);



);

draft saved

draft discarded

Sign up or log in

StackExchange.ready(function ()
StackExchange.helpers.onClickDraftSave('#login-link');
);

Sign up using Google

Sign up using Facebook

Sign up using Email and Password

Post as a guest

Name

Email

Required, but never shown

StackExchange.ready(
function ()
StackExchange.openid.initPostLogin('.new-post-login', 'https%3a%2f%2fstackoverflow.com%2fquestions%2f55392203%2fhow-to-get-eid-from-table-which-is-having-max-number-of-count-in-the-mid%23new-answer', 'question_page');

);

Post as a guest

Name

Email

Required, but never shown

1

You can try below - using top with ties and aggregation

select top 1 with ties id, count(mid) as Mid
from table
group by id
order by count(mid) desc

share|improve this answer

answered Mar 28 at 7:38

fa06fa06

24.9k55 gold badges1111 silver badges2020 bronze badges

add a comment | 

1

You can try below - using top with ties and aggregation

select top 1 with ties id, count(mid) as Mid
from table
group by id
order by count(mid) desc

share|improve this answer

answered Mar 28 at 7:38

fa06fa06

24.9k55 gold badges1111 silver badges2020 bronze badges

add a comment | 

You can try below - using top with ties and aggregation

select top 1 with ties id, count(mid) as Mid
from table
group by id
order by count(mid) desc

share|improve this answer

answered Mar 28 at 7:38

fa06fa06

24.9k55 gold badges1111 silver badges2020 bronze badges

select top 1 with ties id, count(mid) as Mid
from table
group by id
order by count(mid) desc

share|improve this answer

answered Mar 28 at 7:38

fa06fa06

24.9k55 gold badges1111 silver badges2020 bronze badges

answered Mar 28 at 7:38

fa06fa06

24.9k55 gold badges1111 silver badges2020 bronze badges

answered Mar 28 at 7:38

fa06fa06

24.9k55 gold badges1111 silver badges2020 bronze badges

1

use top 1

select top 1 id,count(*) as cnt
from table 
group by id
order by cnt desc

if you need ties then use with ties

select top 1 with ties id,count(*) as cnt
 from table 
 group by id
 order by cnt desc

share|improve this answer

edited Mar 28 at 7:45

answered Mar 28 at 7:37

Zaynul Abadin TuhinZaynul Abadin Tuhin

23.7k44 gold badges1616 silver badges4141 bronze badges

• 
  
  
  

  
  

  
  
  
  
  
  

  
  
  If multiple ids have the same count in that case top 1 will display 1 id only
  
  – mahadev dhyani
  Mar 28 at 7:43
  

  
  
  
  


• 
  
  
  

  
  

  
  
  
  
  
  

  
  
  @mahadevdhyani yes it will show 1 id but if you need all then you have to use ties
  
  – Zaynul Abadin Tuhin
  Mar 28 at 7:44
  

  
  
  
  

add a comment | 

1

use top 1

select top 1 id,count(*) as cnt
from table 
group by id
order by cnt desc

if you need ties then use with ties

select top 1 with ties id,count(*) as cnt
 from table 
 group by id
 order by cnt desc

share|improve this answer

edited Mar 28 at 7:45

answered Mar 28 at 7:37

Zaynul Abadin TuhinZaynul Abadin Tuhin

23.7k44 gold badges1616 silver badges4141 bronze badges

• 
  
  
  

  
  

  
  
  
  
  
  

  
  
  If multiple ids have the same count in that case top 1 will display 1 id only
  
  – mahadev dhyani
  Mar 28 at 7:43
  

  
  
  
  


• 
  
  
  

  
  

  
  
  
  
  
  

  
  
  @mahadevdhyani yes it will show 1 id but if you need all then you have to use ties
  
  – Zaynul Abadin Tuhin
  Mar 28 at 7:44
  

  
  
  
  

add a comment | 

use top 1

select top 1 id,count(*) as cnt
from table 
group by id
order by cnt desc

if you need ties then use with ties

select top 1 with ties id,count(*) as cnt
 from table 
 group by id
 order by cnt desc

share|improve this answer

edited Mar 28 at 7:45

answered Mar 28 at 7:37

Zaynul Abadin TuhinZaynul Abadin Tuhin

23.7k44 gold badges1616 silver badges4141 bronze badges

select top 1 id,count(*) as cnt
from table 
group by id
order by cnt desc

select top 1 with ties id,count(*) as cnt
 from table 
 group by id
 order by cnt desc

share|improve this answer

edited Mar 28 at 7:45

answered Mar 28 at 7:37

Zaynul Abadin TuhinZaynul Abadin Tuhin

23.7k44 gold badges1616 silver badges4141 bronze badges

answered Mar 28 at 7:37

Zaynul Abadin TuhinZaynul Abadin Tuhin

23.7k44 gold badges1616 silver badges4141 bronze badges

answered Mar 28 at 7:37

Zaynul Abadin TuhinZaynul Abadin Tuhin

23.7k44 gold badges1616 silver badges4141 bronze badges