SQL query

[Liou]

I have a table below:

Table
Name id
John 40
Mary 50
Peter 15
John 40
Peter 15
John 40

How can I query in SQL to get who appear most of the times in the table?
(in the case, the answer is John)

please help me¡Kthanks

My email address is
l.liou@student.unsw.edu.au
lunchih@hotmail.com

thanks a lot
primer in Oracle

[Deepak Nayak]

Hi,

Since MSSQL Cannot perform an aggregate function on an expression containing an aggregate or a subquery.

A trick applicable to Sybase and MSSQL Servers

set rowcount 1
Select name , id , count(*)
from drop_me
group by
name , id
order by 3 desc

All the best

------------
Liou at 10/25/99 10:07:15 AM

I have a table below:

Table
Name id
John 40
Mary 50
Peter 15
John 40
Peter 15
John 40

How can I query in SQL to get who appear most of the times in the table?
(in the case, the answer is John)

please help me¡Kthanks

My email address is
l.liou@student.unsw.edu.au
lunchih@hotmail.com

thanks a lot
primer in Oracle

[Mathew]

Don't agree with the SQL posted, if you remove the last line 'order by 3 desc' the query doesn't work. The last line needs some knowledge of the data values and expected result.

------------
Deepak Nayak at 10/25/99 4:33:33 PM

Hi,

Since MSSQL Cannot perform an aggregate function on an expression containing an aggregate or a subquery.

A trick applicable to Sybase and MSSQL Servers

set rowcount 1
Select name , id , count(*)
from drop_me
group by
name , id
order by 3 desc

All the best

------------
Liou at 10/25/99 10:07:15 AM

I have a table below:

Table
Name id
John 40
Mary 50
Peter 15
John 40
Peter 15
John 40

How can I query in SQL to get who appear most of the times in the table?
(in the case, the answer is John)

please help me¡Kthanks

My email address is
l.liou@student.unsw.edu.au
lunchih@hotmail.com

thanks a lot
primer in Oracle

[Nishi Narula]

Here is one way:


Create table #n (Name varchar(10), id int)

insert #n values('John', 40)

insert #n values('Mary', 50)

insert #n values('Peter', 15)

insert #n values('John', 40)

insert #n values('Peter', 15)

insert #n values('John', 40)

insert #n values('Peter', 40)

insert #n values('John', 40)

select Name, count(*) as Num into #k from #n group by Name

select Name, Num from #k where Num=(select max(Num) from #k)

returns the following:

Name Num

---------- -----------

John 4


Nishi

------------
Mathew at 10/26/99 4:52:48 AM


Don't agree with the SQL posted, if you remove the last line 'order by 3 desc' the query doesn't work. The last line needs some knowledge of the data values and expected result.

------------
Deepak Nayak at 10/25/99 4:33:33 PM

Hi,

Since MSSQL Cannot perform an aggregate function on an expression containing an aggregate or a subquery.

A trick applicable to Sybase and MSSQL Servers

set rowcount 1
Select name , id , count(*)
from drop_me
group by
name , id
order by 3 desc

All the best

------------
Liou at 10/25/99 10:07:15 AM

I have a table below:

Table
Name id
John 40
Mary 50
Peter 15
John 40
Peter 15
John 40

How can I query in SQL to get who appear most of the times in the table?
(in the case, the answer is John)

please help me¡Kthanks

My email address is
l.liou@student.unsw.edu.au
lunchih@hotmail.com

thanks a lot
primer in Oracle

[bbott]

This should do. (SQL 92 Version)

SELECT name,COUNT(*)
FROM name_table
GROUP BY ID
HAVING COUNT(*) >= ALL (SELECT COUNT(*)
FROM name_table
GROUP BY id)


Old way, create a view to summarize the
counts, then use the view in a second query

CREATE VIEW id_counter
AS SELECT COUNT(*)AS emp_count
FROM name_table
GROUP BY id

then the 2nd query

SELECT name,count(*)
FROM name_table
GROUP BY ID
HAVING COUNT(*) = (SELECT MAX(emp_count)
FROM id_counter)

These logic examples are nicely described
in "Joe Cleko's SQL Puzzles and Answers."
ISBN 1-55860-453-7 Highly Recommended reading.

bbott

------------
Liou at 10/25/99 10:07:15 AM

I have a table below:

Table
Name id
John 40
Mary 50
Peter 15
John 40
Peter 15







John 40

How can I query in SQL to get who appear most of the times in the table?
(in the case, the answer is John)

please help me¡Kthanks

My email address is
l.liou@student.unsw.edu.au
lunchih@hotmail.com

thanks a lot
primer in Oracle

[Nishi Narula]

Just fixing a little typo in your 1st query bbott :-)

SELECT name,COUNT(*)

FROM name_table

GROUP BY name

HAVING COUNT(*) >= ALL (SELECT COUNT(*) FROM #n GROUP BY name)


------------
bbott at 10/26/99 5:14:20 PM

This should do. (SQL 92 Version)

SELECT name,COUNT(*)
FROM name_table
GROUP BY ID
HAVING COUNT(*) >= ALL (SELECT COUNT(*)
FROM name_table
GROUP BY id)


Old way, create a view to summarize the
counts, then use the view in a second query

CREATE VIEW id_counter
AS SELECT COUNT(*)AS emp_count
FROM name_table
GROUP BY id

then the 2nd query

SELECT name,count(*)
FROM name_table
GROUP BY ID
HAVING COUNT(*) = (SELECT MAX(emp_count)
FROM id_counter)

These logic examples are nicely described
in "Joe Cleko's SQL Puzzles and Answers."
ISBN 1-55860-453-7 Highly Recommended reading.

bbott

------------
Liou at 10/25/99 10:07:15 AM

I have a table below:

Table
Name id
John 40
Mary 50
Peter 15
John 40
Peter 15







John 40

How can I query in SQL to get who appear most of the times in the table?
(in the case, the answer is John)

please help me¡Kthanks

My email address is
l.liou@student.unsw.edu.au
lunchih@hotmail.com

thanks a lot
primer in Oracle