Tag: PostgreSQL

IT

MySQL – How to get Top N rows for each group

We have the following table and data:

CREATE TEMPORARY TABLE girls(
name text,
haircolor text,
score INT
);
 
INSERT INTO girls VALUES ('Megan','brunette',9);
INSERT INTO girls VALUES ('Tiffany','brunette',5);
INSERT INTO girls VALUES ('Kimberly','brunette',7);
INSERT INTO girls VALUES ('Hester','blonde',10);
INSERT INTO girls VALUES ('Caroline','blonde',5);
 
SELECT * from girls;
+----------+-----------+-------+
| name     | haircolor | score |
+----------+-----------+-------+
| Megan    | brunette  |     9 | 
| Tiffany  | brunette  |     5 | 
| Kimberly | brunette  |     7 | 
| Hester   | blonde    |    10 | 
| Caroline | blonde    |     5 | 
+----------+-----------+-------+
5 rows in set (0.00 sec)

Session Variables

MySQL, at least on the versions I've checked, does not support ROW_NUMBER() function that can assign a sequence number within a group, the MySQL session variables can be used to build a workaround. Session variables do not need to be declared first and can be used to do calculations and perform actions based on them. They appear to require initialization. For instance:

Continue Reading…

© GeekLabInfo MySQL - How to get Top N rows for each group is a post from GeekLab.info. You are free to copy materials from GeekLab.info, but you are required to link back to http://www.geeklab.info

1 Star2 Stars3 Stars4 Stars5 Stars (1 votes, average: 1.00 out of 5)
Loading...