Menu
Here is some example data in a mysql table
a b 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 do I query this table such that I get 2 rows per distance selected at random?
A successful query will produce something like
a b 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
947
Selection symbols to narrow row selection To select rows using selection symbols for character or graphic data, use the LIKE keyword in a WHERE clause, and the underscore and percent sign as selection symbols.
Use the COUNT aggregate function to count the number of rows in a table. This function takes the name of the column as its argument (e.g., id ) and returns the number of rows for this particular column in the table (e.g., 5).
To do that, you can use the ROW_NUMBER() function. In OVER() , you specify the groups into which the rows should be divided ( PARTITION BY ) and the order in which the numbers should be assigned to the rows ( ORDER BY ). You assign the row numbers within each group (i.e., year).
Use:
SELECT x.a,
x.b,
x.distance
FROM (SELECT t.a,
t.b,
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, x.a
If you love us? You can donate to us via Paypal or buy me a coffee so we can maintain and grow! Thank you!
Donate Us With
