Menu
If I have a USER table like
class | age -------------- 1 20 3 56 2 11 1 12 2 20 Then I can easily get the youngest user in each class via
select class, min(age) from user group by class; Similarly, by replacing min with max, I can get the oldest. But how can I get the 10th youngest (or oldest) in each class? By the way, I'm using MySql v.5.0.
Cheers,
631
ROW_NUMBER (Window Function) ROW_NUMBER (Window Function) is a standard way of selecting the nth row of a table. It is supported by all the major databases like MySQL, SQL Server, Oracle, PostgreSQL, SQLite, etc.
Here's the SQL query to select every nth row in MySQL. mysql> select * from table_name where table_name.id mod n = 0; In the above query, we basically select every row whose id mod n value evaluates to zero.
The SQL GROUP BY clause is used in a SELECT statement to collect data across multiple records and group the results by one or more columns. A SELECT statement clause that divides the query result into groups of rows, usually for the purpose of performing one or more aggregations on each group.
Select * from any table will fetch and display all the column in that table, while Select 1 from any table will display one row with 1 without any column name.
SELECT a.class, ( SELECT b.age FROM users b WHERE b.class = a.class ORDER BY age LIMIT 1,1 ) as age FROM users a GROUP BY a.class Would get the 2nd youngest in each class. If you wanted the 10th youngest, you'd do LIMIT 9,1 and if you wanted the 10th oldest, you'd do ORDER BY age DESC.
180
Here N presents Nth record oldest
SELECT * FROM users k WHERE N = (SELECT COUNT( DISTINCT age) FROM users u WHERE k.age >= u.age AND k.class = u.class GROUP BY u.class) and it gives Nth record youngest
SELECT * FROM users k WHERE N = (SELECT COUNT(DISTINCT age) FROM users u WHERE k.age <= u.age AND k.class = u.class GROUP BY u.class) 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
