Newbie - Need to create a list of people that are most often listed.

[Junior1544]

I have a table that contains a list of people and every time they've logged some thing. Sorta like a history table.

What I need is an sql statement that will return the top ten people that have the most records...

The data in the following fields can be the critera for for if this person has logged in...

Name and SSN

If I need to describe this any better please ask, and I'd be happy to. I am very much a novice when it comes to SQL and can only make the most basic of select or update queries....

Thank you.

--James

junior1544@jmjpc.net
Life is change. To deny change is to deny life.

 

[jby1]

select top 10 staffname
from [table]
group by staffname
order by count(staffname) desc

if you also want to see how many times each person logged in:

select top 10 staffname, count(staffname)
from [table]
group by staffname
order by count(staffname) desc

 

[Junior1544]

That looks great...

How would I go about having it return a few other fields with that name aswell...

Just fields such as Phone number or other things...

I'd like to have about 4 more fields that are already in the database included... They would not be sorted against, or part of the top 10 section though...

Any thoughts?

--James

junior1544@jmjpc.net
Life is change. To deny change is to deny life.

 

[SQLBill]

Just add them to the select and the GROUP BY.

select top 10 staffname, phone, address
from [table]
group by staffname, phone, address
order by count(staffname) desc

-SQLBill

 

[Junior1544]

That does return everything... But it's not exacly what I need...

it's close though... And thanks for helping... I'll try to refine it a little better...

the way you describe, it will return the same person multiple times... I just need each person listed once, with the latest info that had been put in... This is for a Log... The way it will need to work is that this will return the top ten most entries(one per person) of who's called in, and the latest entry info for each...

This way we know who the 10 people that call in the most are and their latest info...

Am I going about this the wrong way, is there an easier way?? Am I still not being clear enough??

I've tried working with each of the way's you've given me, they all work, but they don't do what I need them to...

I'm confused...

Sorry...

--James

junior1544@jmjpc.net
Life is change. To deny change is to deny life.

 

[SQLBill]

Then you need the DISTINCT keyword:

select top 10 [b]DISTINCT[/b] staffname, phone, address
from [table]
group by staffname, phone, address
order by count(staffname) desc

-SQLBill