CODEWITHSUNDEEP
mysql
sineverba
sineverba

Reputation: 5172

MySQL Query limit 3 or more if last 3 are equal?

I need to limit my result to 3 row (an example), but if 3rd result is equal to 4th, print also 4th, and so on. To explain: from this table

id         punteggio
1          100
2          200
3          70
4          100
5          54
6          201
7          200

if I do

SELECT * FROM table ORDER BY punteggio DESC LIMIT 3

i need in every case to print following situation:

id         punteggio
6           201
2           200
7           200
1           100
4           100

Because my "three" best points are in reality 5 of 7, cause 2 and 7 have same points, like 1 and 4...

I dont'know in advance min and max points, otherwise i would do 

"WHERE punteggio >= 100"

Thank you very much!

UPDATE

Unfortunately my scenario changed:

punteggio born from SUM from another table:

id          idPersona              punteggio
1             1                        30
2             1                        -10
3             2                        50
4             3                        60
5             2                        -10
6             3                        150
7             1                        190

and so on...

i've tried do:

SELECT  persone.nome,
SUM(transazioni.importoFinale) AS punti
FROM    transazioni
INNER JOIN persone ON persone.idPersona = transazioni.idPersona
INNER JOIN 

(SELECT DISTINCT(SUM(transazioni.importoFinale)) AS punti,
persone.nome
FROM    transazioni
INNER JOIN persone on persone.idPersona = transazioni.idPersona
GROUP BY persone.idPersona
ORDER BY punti DESC
LIMIT 3) subq ON transazioni.punti = subq.punti
ORDER BY punti DESC

but it doens't function...

Thank you to all!

Upvotes: 1

Views: 240

Answers (2)

Marc B
Marc B

Reputation: 360602

SELECT id, punteggio
FROM yourtable
WHERE punteggio IN (
   SELECT * FROM (
       SELECT DISTINCT punteggio
       FROM yourtable
       ORDER BY punteggio DESC
       LIMIT 3
   ) AS temp
);

Note that the select * is present to work around mysql not supporting limits in subqueries in an IN() clause.

Upvotes: 1

Michael Berkowski
Michael Berkowski

Reputation: 270609

Use a subquery join to get the DISTINCT set of 3 greatest values for punteggio and join it against the main table to retrieve all rows that have those values.

SELECT
  id,
  punteggio
FROM
  yourtable
  /* subquery gets the top 3 values only */
  /* and the INNER JOIN matches it to all rows in the main table having those values */
  INNER JOIN (
     SELECT DISTINCT punteggio as p
     FROM yourtable
     ORDER BY punteggio DESC
     LIMIT 3
  ) subq ON yourtable.punteggio = subq.p
ORDER BY punteggio DESC

Here's a demo on SQLFiddle.com

Upvotes: 1

Related Questions