Geek Answers Handbook

How to choose a fixed number of rows for each group?

Here are some sample data in mysql table

ab distance 15 44 250 94 31 250 30 41 250 6 1 250 95 18 250 72 84 500 14 23 500 55 24 500 95 8 500 59 25 500 40 73 500 65 85 500 32 50 500 31 39 500 22 25 500 37 11 750 98 39 750 15 57 750 9 22 750 14 44 750 69 22 750 62 50 750 89 35 750 67 65 750 74 37 750 52 36 750 66 53 750 82 74 1000 79 22 1000 98 41 1000

How can I query this table so that I get 2 rows at a distance chosen at random?

A successful request will produce something like

  ab distance 30 41 250 95 18 250 59 25 500 65 85 500 15 57 750 89 35 750 79 22 1000 98 41 1000
+7
sql mysql analytic-functions
source share
3 answers

Using:

 SELECT xa, xb, x.distance FROM (SELECT ta, tb, t.distance CASE WHEN @distance != t.distance THEN @rownum := 1 ELSE @rownum := @rownum + 1 END AS rank, @distance := t.distance FROM TABLE t JOIN (SELECT @rownum := 0, @distance := '') r ORDER BY t.distance --important for resetting the rownum variable) x WHERE x.rank <= 2 ORDER BY x.distance, xa
+5
source share

One way is to use union. For example:

 (SELECT a, b, distance FROM table WHERE distance = 250 LIMIT 2 ORDER BY RAND()) UNION (SELECT a, b, distance FROM table WHERE distance = 500 LIMIT 2 ORDER BY RAND()) ... ORDER BY distance

I might think about how to get one of them with a single query using distinct = /, but as I said, this will bring you only one.

0
source share

I wonder if this will work?

 SELECT a,b,distance FROM YourTable t2 WHERE ROW(a,b,distance) IN ( SELECT a,b,distance FROM YourTable t1 WHERE t1.distance=t2.distance ORDER BY RAND() LIMIT 2 )

EDIT: Unfortunately not. LIMIT is not supported in a subquery.

0
source share

More articles:


All Articles