Count Distinct Records - SQL Server Forums

Please start any new threads on our new site at https://forums.sqlteam.com. We've got lots of great SQL Server experts to answer whatever question you can come up with.

All Forums

SQL Server 2000 Forums

SQL Server Development (2000)

Count Distinct Records

Author

Topic

Mannga
Yak Posting Veteran

70 Posts

Posted - 2002-01-08 : 10:25:47

Hi All,

I am trying to create a stored procedure at the moment that will return to me the 20 most frequent records.

My database looks something like this.

[ID] [OrgName] [other fields

]

Now if I run a query something like
SELECT DISTINCT(OrgName) FROM table
I get 647 records returned.

Now what I would like to do is return the 20 distinct records which are used more if you get my meaning?

I hope that makes sense to someone out there?

Thanks
Gavin

smccreadie
Aged Yak Warrior

505 Posts

Posted - 2002-01-08 : 10:30:33

How about

SELECT TOP 20 OrgName, COUNT(ID)
FROM table
GROUP BY Orgname
ORDER BY COUNT(ID) DESC

Nazim
A custom title

1408 Posts

Posted - 2002-01-08 : 10:32:21

Do you a field which will keep the count of times the record is used?
if so,
select top 20 distinct distinct id, countfield
from tablename
where countfield>20
order by countfield

Do you have a field which updates its last usage(if you searching for the most 20 last used)?

select top 20 distinct distinct id, lastuseddatetime
from tablename
where lastuseddatetime>20

How will you decide on how many times the record is used, OR are you adding that record with the ID again in the table on its use. there by a record with ID 10 if it has 30 records in the table, it is as good as saying 30 times it is used?.

select id, count(*) from tablename
group by id
having count(*)>20

HTH

----------------------------
Anything that Doesn't Kills you Makes you Stronger

Mannga
Yak Posting Veteran

70 Posts

Posted - 2002-01-08 : 10:40:33

quote:

How about

SELECT TOP 20 OrgName, COUNT(ID)
FROM table
GROUP BY Orgname
ORDER BY COUNT(ID) DESC

Woohoo

.... works perfectly, thanks a lot

Mannga
Yak Posting Veteran

70 Posts

Posted - 2002-01-08 : 11:01:03

Just one more question on this one

...

How can I get my results to be in alphabetical order?
What I would like is now that I have my top 20, for them now to be sorted alphabetically....

Thanks,
Gavin

Nazim
A custom title

1408 Posts

Posted - 2002-01-08 : 11:10:19

Select * from
(
SELECT TOP 20 OrgName, COUNT(ID)
FROM table
GROUP BY Orgname
ORDER BY COUNT(ID) DESC
) a
order by a.OrgName

----------------------------
Anything that Doesn't Kills you Makes you Stronger

Mannga
Yak Posting Veteran

70 Posts

Posted - 2002-01-08 : 11:24:32

When i try to save this procedure I get the error message
'No Column was specified for column 2 of a'

And if I change the procedure to this
Select * from
(
SELECT TOP 20 OrgName, COUNT(ID)
FROM table
GROUP BY Orgname
ORDER BY COUNT(ID) DESC
) a, b
order by a.OrgName

I can then save the procedure but I get this error message when I run it
'Invalid object name 'b'.'???

JustinBigelow
SQL Gigolo

1157 Posts

Posted - 2002-01-08 : 11:41:17

You need to give a name to your aggregated column...

Select * from
(
SELECT TOP 20 OrgName, COUNT(ID) as "topcount"
FROM table
GROUP BY Orgname
ORDER BY COUNT(ID) DESC
) a
order by a.OrgName

hth,
Justin

AndrewMurphy
Master Smack Fu Yak Hacker

2916 Posts

Posted - 2002-01-08 : 11:42:12

i think count(*) needs to be named....

ie select countxyz = count(*) from tablexyz

re the a,b....the 'a' is an alias for the resultset (aka derived table) produced in the subquery...therefore in your example 'b' doesn't exist as a table.....

Mannga
Yak Posting Veteran

70 Posts

Posted - 2002-01-08 : 11:44:05

Cool,

That is exactly what I was looking for,

Thanks to everyone who has helped here..

Cheers,
Gavin

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources