CODEWITHSUNDEEP
mysql
Frank AK
Frank AK

Reputation: 1781

How to query all the attributes of the maximum record in MySQL with simple sql?

The example table as below show.

+-------+------------+------+
| score | created    | uid  |
+-------+------------+------+
|    89 | 2017-08-01 |  101 |
|    69 | 2017-08-02 |  101 |
|    99 | 2017-08-03 |  101 |
|    79 | 2017-08-03 |  102 |
|    69 | 2017-08-04 |  102 |
|    90 | 2017-08-02 |  102 |
+-------+------------+------+

I want to query all user maximum score, so i try the below sql.

select uid, max(score) as max_score from minTable group by uid;

I got a result.

+------+-----------+
| uid  | max_score |
+------+-----------+
|  101 |        99 |
|  102 |        90 |
+------+-----------+

For now, I expect to know what date created is with the maximum rows! I know i can use subquery to get it. like the below sql

select a.uid, a.score, a.created
from minTable a
INNER JOIN 
(select uid, max(score) as max_score from minTable group by uid)b
ON a.uid=b.uid and a.score=b.max_score;

And then it's seems got the expect result.

+------+-------+------------+
| uid  | score | created    |
+------+-------+------------+
|  101 |    99 | 2017-08-03 |
|  102 |    90 | 2017-08-02 |
+------+-------+------------+

After insert more record with the below sql.

insert into minTable (uid, score, created) values (102, 90, "2017-08-01");
insert into minTable (uid, score, created) values (102, 90, "2017-08-05");

I try query with subquery again.

+------+-------+------------+
| uid  | score | created    |
+------+-------+------------+
|  101 |    99 | 2017-08-03 |
|  102 |    90 | 2017-08-02 |
|  102 |    90 | 2017-08-01 |
|  102 |    90 | 2017-08-05 |
+------+-------+------------+

It's more than one record with each uid, I know i can do group again ! like the below way.

SELECT c.uid, c.score, min(c.created) as min_created
FROM (
select a.uid, a.score, a.created
from minTable a
INNER JOIN 
(select uid, max(score) as max_score from minTable group by uid)b
ON a.uid=b.uid and a.score=b.max_score)c GROUP BY c.uid, c.score;

Got the expect result:

+------+-------+-------------+
| uid  | score | min_created |
+------+-------+-------------+
|  101 |    99 | 2017-08-03  |
|  102 |    90 | 2017-08-01  |
+------+-------+-------------+

I want to know is there any simple way to do this query ?

Upvotes: 2

Views: 91

Answers (3)

Strawberry
Strawberry

Reputation: 33945

Here's a method (albeit one with certain vulnerabilities), but anyway I prefer yours...

SELECT *
  FROM mintable
 WHERE FIND_IN_SET(id,(SELECT GROUP_CONCAT(id ORDER BY score DESC,created) FROM mintable)) <= 2;
+----+-------+------------+------+
| id | score | created    | uid  |
+----+-------+------------+------+
|  3 |    99 | 2017-08-03 |  101 |
|  7 |    90 | 2017-08-01 |  102 |
+----+-------+------------+------+

Upvotes: 1

P.Salmon
P.Salmon

Reputation: 17655

You could do it by assigning a row number using a variable , but I prefer your solution.

drop table if exists mintable;
create table mintable(score int,  created date, uid int);
insert into mintable values
(    89 , '2017-08-01' ,  101), 
(    69 , '2017-08-02' ,  101), 
(    99 , '2017-08-03' ,  101), 
(    79 , '2017-08-03' ,  102), 
(    69 , '2017-08-04' ,  102), 
(    90 , '2017-08-02' ,  102), 
(    90 , '2017-08-01' ,  102),
(     90 , '2017-08-05' ,  102);



select s.uid,s.created,s.score from
(
select m.uid,m.created,m.score,
         if(m.uid <> @p ,@rn:=1,@rn:=@rn+1) rn ,
        @p:=m.uid
from mintable m ,(select @rn:=0,@p:=0) r
order by m.uid,m.score desc,created
) s where s.rn = 1

Result

+------+------------+-------+
| uid  | created    | score |
+------+------------+-------+
|  101 | 2017-08-03 |    99 |
|  102 | 2017-08-01 |    90 |
+------+------------+-------+
2 rows in set (0.00 sec)

Upvotes: 1

nacho
nacho

Reputation: 5396

Yes, there it's an easiest way, just grouping by:

select uid, max(score) as max_score,min(c.created) as min_created  
from minTable 
group by uid,score;

Upvotes: -1

Related Questions