0

Possible Duplicate:
mysql: Using LIMIT within GROUP BY to get N results per group?

I need to perform a select that will return (n) number of records per unique us_state.

Basically, I would like to find an equivalent to TOP(n), but since MySQL doesn't support that, is there a similar command or method I can use to shortcut having to right specialized code to perform the same operation?

Thanks.

Community
  • 1
  • 1
Skittles
  • 2,866
  • 9
  • 31
  • 37
  • 1
    `Top n` does not return the top per state. Are you sure that is what you want? – Gordon Linoff Jan 14 '13 at 21:30
  • Basically, I want a sample of, say 5, records for each occurrance of a state in the us_states column. I have someone that wants samples from each and would prefer to not have to write code to do that. – Skittles Jan 14 '13 at 21:51
  • @bluefeet - Write your comment up as an answer. I'll just accept yours as it's likely going to get me much closer to a solution using the self-join method. – Skittles Jan 14 '13 at 22:04

2 Answers2

1

What you are trying to do is select a number of records per group. This can be done by using variables in your query similar to this:

select *
from
(
    SELECT sid, 
        state, 
        votes,
        @prev := @curr,
        @curr := state,
        @rank := IF(@prev = @curr, @rank+1, 1) AS rank
    FROM
    (
      select t1.sid, state, votes
      FROM table1 t1
      INNER JOIN table2 t2
          ON t1.sid=t2.sid
    ) src, (SELECT @curr := null, @prev := null, @rank := 1) r
    ORDER BY state, votes desc
) src
where rank <= 2
order by state, votes;

See SQL Fiddle with Demo

There are other ways to do this which are outlined here:

Using LIMIT within GROUP BY to get N results per group?

Community
  • 1
  • 1
Taryn
  • 242,637
  • 56
  • 362
  • 405
0

Limit optimization in MySQL does just that.

Edit

Since you want them order by some type of column or something, you also need ORDER BY clause.

Community
  • 1
  • 1
hjpotter92
  • 78,589
  • 36
  • 144
  • 183
  • They don't necessarily have to be ordered. Just need a sampling of n number of records for each distinct occurrence of a us state in my states column. – Skittles Jan 14 '13 at 21:53