limiting a query to a specific item

Question

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

I have a db with thousands of domains and data. I want to list only 100 rows of each domain. i can't use LIMIT 100 cause that only limits 100 records but I want dom1 to list 100 dom2 to list 100 dom3 to list 100

all in one query. This is what I have so far.

    SELECT domain COUNT(Key) AS DomCount
    FROM table_domain
    GROUP BY user,location
    ORDER BY domain,DomCount DESC

Could I use a CASE?

he talks about getting MIN or MAX of each group. I want to get just a certain number of rows per group. — Dev-Ria, Dec 14 '12 at 17:04

rs. · Answer 1 · 2012-12-14T17:15:46.643

0

Try this

See DEMO for following query here

SELECT key,domain FROM (
SELECT key,domain,
   @drank:=CASE 
               WHEN @domain_v <> domain THEN 1 
               ELSE @drank+1 END AS rn,
   @domain_v:= domain AS domain_set
FROM
  (SELECT @drank:= -1) nr,
  (SELECT @domain_v:= -1) n,
  (SELECT * FROM TableName ORDER BY domain) t
  ) x WHERE rn < 101

edited Dec 14 '12 at 17:15

answered Dec 14 '12 at 17:01

rs.

26,707
12
68
90

seems to be a syntax error at END AS rn – Dev-Ria Dec 14 '12 at 17:11
@Dev-Ria it works check my demo link, i'm returning 2 per domain – rs. Dec 14 '12 at 17:16
Ok it works but it timed out cause it takes too long. Could be because of the number of records I have in my db. I changed the limit to 2 like yours and still no luck – Dev-Ria Dec 14 '12 at 18:31
any ideas on how to optimize it? – Dev-Ria Dec 17 '12 at 13:09

limiting a query to a specific item

1 Answers1