Solved

how to do a sql statment on this question

Posted on 1998-12-10
5
181 Views
Last Modified: 2010-03-19
say I have a table like this

chris 01
chris 02
chris 09
kim   01
kim   08
Gal   04
Gal   06
Gal   07
Gal   08

I want to find the person with most records in this table.
in this case, it will return: Gal 4
How do I do in sql statment to do this. please don't use Top 1 in Access. It needs to be done in 1 query, you could have subquery but it has to be 1 query.

thank you very much

win 100 pts!!! :)
ny971181
0
Comment
Question by:ny971181
[X]
Welcome to Experts Exchange

Add your voice to the tech community where 5M+ people just like you are talking about what matters.

  • Help others & share knowledge
  • Earn cash & points
  • Learn & ask questions
5 Comments
 
LVL 7

Expert Comment

by:spiridonov
ID: 1092210
set rowcount 1
select field1,count(field2) from table1
group by field1
order by count(field2) desc
set rowcount 0
0
 
LVL 1

Author Comment

by:ny971181
ID: 1092211
Can we use Set? I'v never heard of this keyword.
I got this error msg: Invalid syntax. Set was hilighted.

Could anyone give me any other suggestion?
Thank you very much

ny971181
0
 
LVL 3

Expert Comment

by:cognition
ID: 1092212
Are you using SQL Server, and what is the client, VB, Crystal etc ?

If you are using SQL server then spridonov's solution will work. You can put all the above in a stored procedure.
0
 
LVL 2

Accepted Solution

by:
odessa earned 100 total points
ID: 1092213
This Works fine I think
select table.f1, ff
from table join (select f1 , count(f2) as ff from table group by f1) as ko on ko.f1 = table.f1
where ff = (select max(ff) from (select f1 , count(f2) as ff from table group by f1) as bb)
group by table1.f1, ff

0
 
LVL 1

Author Comment

by:ny971181
ID: 1092214
Thanks
0

Featured Post

DevOps Toolchain Recommendations

Read this Gartner Research Note and discover how your IT organization can automate and optimize DevOps processes using a toolchain architecture.

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

For both online and offline retail, the cross-channel business is the most recent pattern in the B2C trade space.
A Stored Procedure in Microsoft SQL Server is a powerful feature that it can be used to execute the Data Manipulation Language (DML) or Data Definition Language (DDL). Depending on business requirements, a single Stored Procedure can return differe…
Via a live example, show how to backup a database, simulate a failure backup the tail of the database transaction log and perform the restore.
Viewers will learn how the fundamental information of how to create a table.

738 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question