CODEWITHSUNDEEP
sqlsql-server
Mr.Bear
Mr.Bear

Reputation: 37

SQL Selecting Top 1 with MAX

In a parking citation database I am trying to select the state that has the most citations.

SELECT State, COUNT(Citation) as MostViolations
FROM dbo.ParkingCitations
GROUP BY State
ORDER BY COUNT(Citation) DESC

The syntax above will give me the top state in the first row, but I'm not sure how I got about selecting just that distinct state only? (Should I be using a subquery with TOP 1 somehow?)

Upvotes: 0

Views: 851

Answers (1)

Gordon Linoff
Gordon Linoff

Reputation: 1269563

I think TOP 1 does what you want:

SELECT TOP 1 State, COUNT(Citation) as MostViolations
FROM dbo.ParkingCitations
GROUP BY State
ORDER BY COUNT(Citation) DESC;

If you want all when there are ties, then use top 1 with ties.

Upvotes: 2

Related Questions