need code to find most common

[nutpuk]

Hi I have a table of cars in sql server 2000 eg ford, mazda, GM the list is endless, Im wanting to find out the most common car in the table but I dont know the sql select statement to do it.

The cloumn with the cars in is call model and the table is car But i dont know what to put in to find the most common make
?????????

Select model
From Car

 

[carp]

Try
SELECT model, count(*)
FROM car
GROUP BY model
HAVING count(*) = (SELECT MAX(count(*))
FROM car
GROUP BY model);

I don't know if this will work with SQL Server - it does in Oracle and I believe this is ANSI-compliant.

 

[nutpuk]

Thankyou so much. I can now crack on, hopefully i should be ok now

I owe you

 

[PHV]

You may try something like this:
SELECT TOP 1 model, Count(*)
FROM car
GROUP BY model
ORDER BY 2 DESC

Hope This Helps, PH.
Want to get great answers to your Tek-Tips questions? Have a look at FAQ219-2884 or FAQ222-2244