To find user who having worked in the maximum number company

Question

1.00/5 (2 votes)

See more:

Write a query to display the name of the alumni user(s) (Role-'Alumni') who has/have worked in maximum number of companies, sorted by name.

SQL

profile_id  company_name
1           Tcs
2           cts
3           hcl
1           hexa
4           mindtree
3           cts
1           cts

so the answer should be

SQL

profile-id    company_count
    1             3

This my query

SQL

select u.name,Count(distinct ex.company_name)
from user u
inner join role r on (r.id = u.role_id)
inner join experience ex on (ex.profile_id = u.profile_id)
where r.name ="Alumni"
group by u.name
--having Count(distinct ex.company_name) in (select max(ex.company_name) from experience ex where ex.profile_id = u.profile_id)
order by u.name

Posted 16-Apr-15 1:29am

Harshit Wadhera

Updated 16-Apr-15 2:33am

v5

Add a Solution

Comments

ZurdoDev 16-Apr-15 7:37am

This is quite easy to do. Where are you stuck?

Harshit Wadhera 16-Apr-15 7:49am

i know to count the distinct name but how find max that i don"t know.

ZurdoDev 16-Apr-15 7:51am

You use the MAX() function. See Solution 1.

Harshit Wadhera 16-Apr-15 8:02am

it's giving me error

ZurdoDev 16-Apr-15 8:07am

1. Then give the error so we don't have to guess.
2. I did look at the solution and you posted the error there. The problem is you tagged this as SQL and not MySql. I suggest update your question so that it says MySql.

Peter Leow 16-Apr-15 7:42am

How do you know which profile_id belongs to 'Alumni' role? There should be another table right?

Harshit Wadhera 16-Apr-15 7:56am

it's giving me error

Harshit Wadhera 16-Apr-15 7:48am

yupp but that i will know that r.name='Alumni'

Harshit Wadhera 16-Apr-15 7:54am

This my query

<pre lang="sql">select u.name,Count(distinct ex.company_name)
from user u
inner join role r on (r.id = u.role_id)
inner join experience ex on (ex.profile_id = u.profile_id)
where r.name ='Alumni'
group by u.name
--having Count(distinct ex.company_name) in (select max(ex.company_name) from experience ex where ex.profile_id = u.profile_id)
order by u.name,max(ex.company_name)</pre>

Peter Leow 16-Apr-15 20:55pm

First of all, you don't even bother to explain your question with sufficient details, such as the existence of additional tables, You have also tagged the question wrongly. That waste everybody's time and effort, which include yours as it delays you from getting the answer. Keep saying "it's giving me error." is meaningless, We cannot see your screen. I suspect you have little or no knowledge of SQL, where did you get this code from?

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)

Sandeep Mogaveer · Answer 1 · 2015-04-16T01:47:00

Solution 1

SQL

SELECT TOP 1 profile_id,COUNT(company_name) company_count FROM COMPANY GROUP BY profile_id ORDER BY MAX(company_name) DESC

Posted 16-Apr-15 1:47am

Sandeep Mogaveer

Comments

Harshit Wadhera 16-Apr-15 7:53am

i am getting this error
Check the manual that corresponds to your MySQL server version for the right syntax

vangapally Naveen Kumar 16-Apr-15 8:30am

see this link you may get an idea regarding ERROR:
http://stackoverflow.com/questions/21509026/error-in-your-sql-syntax-check-the-manual-that-corresponds-to-your-mysql-server

Sandeep Mogaveer 16-Apr-15 8:32am

i wrote this assuming that you are using SQL .. not My SQL!!

Harshit Wadhera 16-Apr-15 8:35am

can you tell me query for mysql

Sandeep Mogaveer 16-Apr-15 8:45am

No.Sorry I don't use MySQL

vangapally Naveen Kumar · Answer 2 · 2015-04-16T03:11:00

You are using the Reserved word User as table name in Your query .insted of user use [user]
Here explained
http://stackoverflow.com/questions/23446377/syntax-error-due-to-using-a-reserved-word-as-a-table-or-column-name-in-mysql[^]

try like this:

SQL

select u.name,Count(distinct ex.company_name)
from [user] u
inner join role r on (r.id = u.role_id)
inner join experience ex on (ex.profile_id = u.profile_id)
where r.name =&quot;Alumni&quot;
group by u.name
--having Count(distinct ex.company_name) in (select max(ex.company_name) from experience ex where ex.profile_id = u.profile_id)
order by u.name

.