Fetch the row which has the Max value for two column in MySql

Question

i have a table which contains id , primaryid , data ,dataname columns i want only rows for which contains max id and primaryid

create table #temp
(
    id int,
    primaryid int,
    data   nvarchar(20),
    data_name   nvarchar(30)
)


insert into #temp
values (1,1,'3223sfd','434'),(1,2,'sdfsd','dfsdfsd'),
       (1,3,'sdfs897d','898'),(1,4,'898','545'),(1,5,'898','uuyu'),
       (2,1,'3223sfd','434'),(2,2,'sdfsd','dfsdfsd'),
       (2,3,'sdfs897d','898'),(2,4,'898','545'),(2,5,'898','uuyu')

i achieve this with below query

select T.id , T.primaryid , T.data , T.data_name from #temp T , (select ID, max(primaryid) rank from #temp t2  group by id ) as T2
where t.primaryid = t2.rank group by T.id , T.primaryid , T.data , T.data_name

but my table have more than 100k records i want to worry about that

What will be optimized query for this?

Your code is not valid MySQL. What database are you really using? — Gordon Linoff, May 04 '18 at 10:59
[_groupwise max_](http://mysql.rjweb.org/doc.php/groupwise_max) — Rick James, May 18 '18 at 22:32

score 2 · Answer 1 · edited May 04 '18 at 12:29

2

You may use subquery here :

select * 
from #temp t
where primaryid = (select max(tt.primaryid) from #temp tt where tt.id = t.id);

edited May 04 '18 at 12:29

Gordon Linoff

1,242,037
58
646
786

answered May 04 '18 at 10:43

Yogesh Sharma

49,870
5
26
52

This is the best solution for the two databases. – Gordon Linoff May 04 '18 at 12:29

score 1 · Answer 2 · edited May 04 '18 at 11:17

1

You appear to be using SQL Server. If so, one method is:

select top (1) with ties t.*
from #temp t
order by row_number() over (partition by id order by primaryid desc);

edited May 04 '18 at 11:17

Yogesh Sharma

49,870
5
26
52

answered May 04 '18 at 11:01

Gordon Linoff

1,242,037
58
646
786

you query is giving error An expression of non-boolean type specified in a context where a condition is expected, near ';'. – Niteesh Kumar May 04 '18 at 11:04
@NiteeshKumar... Use `order by` instead of `where` clause. – Yogesh Sharma May 04 '18 at 11:16

score 0 · Answer 3 · answered May 04 '18 at 10:45

First you should create index on id and primaryid and then use join as below:

SELECT T.id , T.primaryid , T.data , T.data_name FROM #temp T
JOIN (select id, max(primaryid) as primaryid from #temp t2  group by id ) as T2
ON T.id = t2.id and t.primaryid = t2.primaryid

Fetch the row which has the Max value for two column in MySql

3 Answers3