0

new to SQL, excuse the noob question

votes

vote_id     vote_date
1           2013-08-11
2           2013-08-12
3           2013-08-12
4           2013-08-12
5           2013-08-13
6           2013-08-14

I would like to select the most popular date (2013-08-12) as well as how many votes were cast on that date(3).

CptRayMar
  • 133
  • 4
  • 19
  • Basically a dupe of http://stackoverflow.com/questions/12113699/get-top-n-records-for-each-group-of-grouped-results – Marc B Aug 12 '13 at 16:06

1 Answers1

3

Here's one way to get the specified result:

SELECT v.vote_date
     , COUNT(1) AS votes_cast
  FROM votes v
 GROUP
    BY v.vote_date
 ORDER
    BY votes_cast DESC
     , v.vote_date DESC
 LIMIT 1

The GROUP BY clause allows groups of rows to be aggregated into a single row. In this example, we are "grouping" rows that have the same value of vote_date. The COUNT function is an aggregate function, which operates on all rows within a group, and returns a single value for the group. (COUNT starts at zero, and for each row that has a non-null value for the expression, one is added to the count.) You could also use SUM(1) in place of COUNT(1) in this query.

The ORDER BY returns the rows in the specified sequence. In this example, we want the row that has the highest value for COUNT. So we sort the rows on this expression in descending order. In the case of two or more vote_date with the same "highest" count, we'll return the latest vote_date first.

The LIMIT n clause restricts the number of rows returned, so that only n rows are returned.

(This is not the only SQL statement that will return the specified result, it's just one example.)

spencer7593
  • 106,611
  • 15
  • 112
  • 140
  • What are `1` and `2`? Is there a benefit to using that as opposed to full names? – kush Aug 12 '13 at 16:08
  • 2
    Great, this works perfectly. Will accept as correct answer 'in 11 minutes' when I'm allowed to. – CptRayMar Aug 12 '13 at 16:09
  • 3
    bad idea to use positional field notation. It's entirely too easy to totally break a system, e.g by adding a new field to the select field list, but forgetting to update the order clause. – Marc B Aug 12 '13 at 16:10