Group Top N records

Source: Internet
Author: User
Tags oracle rownum

I often see problems, how to retrieve the first N records of each group. For your convenience, I will list several common solutions.

Problem: The table is as follows. The first two names of each class must be taken out (the second name can be used together)
Table1
+ ---- + ------ + ----- +
| ID | sname | clsno | score |
+ ---- + ------ + ----- +
| 1 | AAAA | C1 | 67 |
| 2 | BBBB | C1 | 55 |
| 3 | CCCC | C1 | 67 |
| 4 | dddd | C1 | 65 |
| 5 | eeee | C1 | 95 |
| 6 | FFFF | C2 | 57 |
| 7 | GGGG | C2 | 87 |
| 8 | hhhh | C2 | 74 |
| 9 | iiii | C2 | 52 |
| 10 | jjjj | C2 | 81 |
| 11 | KKKK | C2 | 67 |
| 12 | llll | C2 | 66 |
| 13 | mmmm | C2 | 63 |
| 14 | NNNN | C3 | 99 |
| 15 | oooo | C3 | 50 |
| 16 | PPPP | C3 | 59 |
| 17 | qqqq | C3 | 66 |
| 18 | RRRR | C3 | 76 |
| 19 | ssss | C3 | 50 |
| 20 | tttt | C3 | 50 |
| 21 | UUUU | C3 | 64 |
| 22 | vvvv | C3 | 74 |
+ ---- + ------ + ----- +
The result is as follows:
+ ---- + ------ + ----- +
| ID | sname | clsno | score |
+ ---- + ------ + ----- +
| 5 | eeee | C1 | 95 |
| 1 | AAAA | C1 | 67 |
| 3 | CCCC | C1 | 67 |
| 7 | GGGG | c2 | 87 |
| 10 | jjjj | C2 | 81 |
| 14 | NNNN | C3 | 99 |
| 18 | RRRR | C3 | 76 |
+ ---- + ------ + ----- +

Method 1:
SelectA. ID, A. sname, A. clsno, A. Score
FromTable1Left
JoinTable1 BOnA. clsno = B. clsno
AndA. score <B. Score
Group ByA. ID, A. sname, A. clsno, A. Score
Having Count(B. ID) <2
Order ByA. clsno, A. Score
Desc

Method 2:

Select*
FromTable1
Where2> (Select
Count(*)FromTable1
WhereClsno = A. clsno
AndScore> A. Score)
Order ByA. clsno, A. Score
Desc

 

Method 3:
Select*
FromTable1
WhereIDIn(SelectID
FromTable1WhereClsno = A. clsno
Order ByScore
Desc limit 2)
Order ByA. clsno, A. Score
Desc

 

Method ....

Here lists the implementation of a variety of SQL statements, some are unique to MySQL (limit, other databases can be changed according to the actual, such as Oracle rownum, ms SQL Server top ,..), sometimes it is supported by SQL standards. However, the efficiency may be different from the application scenarios. You can select an index based on the actual table record.

 

 

Special case n = 1, that is, take the largest/smallest record.
+ ---- + ------ + ----- +
| ID | sname | clsno | score |
+ ---- + ------ + ----- +
| 5 | eeee | C1 | 95 |
| 7 | GGGG | C2 | 87 |
| 14 | NNNN | C3 | 99 |
+ ---- + ------ + ----- +

Select *
From Table1
Where not exists (select 1 from Table1 where clsno = A. clsno and score> A. Score );

 

 

Select .*
From Table1 a inner join (select clsno, max (score) as mscore from Table1 group by clsno) B
On a. clsno = B. clsno and A. Score = B. Score


Select *
From (select * From Table1 order by score DESC) T
Group by clsno

Contact Us

The content source of this page is from Internet, which doesn't represent Alibaba Cloud's opinion; products and services mentioned on that page don't have any relationship with Alibaba Cloud. If the content of the page makes you feel confusing, please write us an email, we will handle the problem within 5 days after receiving your email.

If you find any instances of plagiarism from the community, please send an email to: info-contact@alibabacloud.com and provide relevant evidence. A staff member will contact you within 5 working days.

A Free Trial That Lets You Build Big!

Start building with 50+ products and up to 12 months usage for Elastic Compute Service

  • Sales Support

    1 on 1 presale consultation

  • After-Sales Support

    24/7 Technical Support 6 Free Tickets per Quarter Faster Response

  • Alibaba Cloud offers highly flexible support services tailored to meet your exact needs.