CODEWITHSUNDEEP
mysql
long hoangvan
long hoangvan

Reputation: 141

how to select all of duplicate record in mysql

My records is: 

name    |   id  |   AVG(point) as point
a       |   1   |   6
b       |   2   |   6
c       |   3   |   5
d       |   4   |   5
e       |   5   |   4
f       |   6   |   3
g       |   7   |   2

How to select record below:

1.I want to select top 3 record, result follow: 

name    |   id  |   AVG(point) as point
a       |   1   |   6
b       |   2   |   6
c       |   3   |   5
d       |   4   |   5
e       |   5   |   4

2.I want to select record not into top 3, result follow:

name    |   id  |   AVG(point) as point
f       |   6   |   3
g       |   7   |   2

How can I do?

Upvotes: 0

Views: 58

Answers (2)

user2260040
user2260040

Reputation: 1380

select * 
from yourtable as t1
inner join (select distinct point 
                from yourtable 
                order by 1 desc 
                limit 3) as t2
on t1.point = t2.point

For the second part of your question, do not use

desc

Upvotes: 0

sgeddes
sgeddes

Reputation: 62831

There are several ways to do these. Here's a couple using in and not in.

For the top 3, you can use in:

select * 
from yourtable 
where point in (select distinct point 
                from yourtable 
                order by 1 desc 
                limit 3)

For the rest, use not in instead:

select * 
from yourtable 
where point not in (select distinct point 
                from yourtable 
                order by 1 desc 
                limit 3)

Other methods include exists with not exists and distinct with joins.

Upvotes: 2

Related Questions