CODEWITHSUNDEEP
mysqlsql
Sahat Yalkabov
Sahat Yalkabov

Reputation: 33684

Find the year that has the maximum crash rates?

SELECT MAX(crash_year) AS crash_year, COUNT(crash_year) AS COUNT 
FROM nyccrash 
GROUP BY crash_year 
ORDER BY COUNT(crash_year) DESC;

This statement will return the entire range for 20 years. But how do I retrieve just the top row?

+------------+-------+
| crash_year | count |
+------------+-------+
|       1999 |   862 |
|       1990 |   847 |
|       1992 |   812 |
|       1991 |   810 |
|       1993 |   806 |
|       2000 |   803 |
|       1994 |   782 |
|       1989 |   763 |
|       2001 |   729 |
|       1995 |   698 |
|       1997 |   672 |
|       1996 |   671 |
|       1998 |   665 |
|       2002 |   497 |
|       2003 |   419 |
|       2006 |   384 |
|       2004 |   377 |
|       2007 |   364 |
|       2005 |   363 |
+------------+-------+

Upvotes: 1

Views: 64

Answers (4)

Mosty Mostacho
Mosty Mostacho

Reputation: 43494

Not sure why you have a MAX on crash_year. Remove it. So the question is:

Find the year that has the maximum crash rates?

In MySQL and PostgreSQL:

SELECT crash_year
FROM nyccrash 
GROUP BY crash_year 
ORDER BY COUNT(crash_year) DESC
LIMIT 1

In SQLServer:

SELECT TOP 1 crash_year
FROM nyccrash 
GROUP BY crash_year 
ORDER BY COUNT(crash_year) DESC

In DB2:

SELECT crash_year
FROM nyccrash 
GROUP BY crash_year 
ORDER BY COUNT(crash_year) DESC
FETCH FIRST 1 ROWS ONLY

Upvotes: 3

Larry Lustig
Larry Lustig

Reputation: 51000

Depends on the SQL database engine you're using. Some use TOP 1 right after SELECT, some use LIMIT 1 after the ORDER BY clause.

Also, you don't need MAX() around the first instance of crash_year.

Upvotes: 4

wyqydsyq
wyqydsyq

Reputation: 2030

Add LIMIT 1 to the end of your query, so:

SELECT MAX(crash_year) AS crash_year, COUNT(crash_year) AS COUNT 
FROM nyccrash 
GROUP BY crash_year 
ORDER BY COUNT(crash_year) DESC
LIMIT 1;

Upvotes: 4

Dan P
Dan P

Reputation: 1999

SELECT TOP 1
    crash_year, COUNT
FROM
(
    SELECT MAX(crash_year) AS crash_year, COUNT(crash_year) AS COUNT 
    FROM nyccrash 
    GROUP BY crash_year 
    ORDER BY COUNT(crash_year) DESC;
) AS T

just saw a LIMIT 1 vs a top 1. I suppose if you added your flavor of SQL as a tag we could be more specific.

Upvotes: 2

Related Questions