ace
ace

Reputation: 6825

get the row with the highest value in MySQL

I want to get the highest value but group by another field on the same table ex:

seqid + fileid + name

1  |    1 | n1
2  |    1 | n2
3  |    2 | n3
4  |    3 | n4
5  |    3 | n5

the result must be

seqid + fileid + name

2  |    1 | n2
3  |    2 | n3
5  |    3 | n5

note: all the field must be display like using select * I'll appreciate any help.tnx

Upvotes: 0

Views: 460

Answers (3)

Valentin Kuzub
Valentin Kuzub

Reputation: 12073

Describe exactly what you need, provide bigger table, and result table, also I dont get it what you mean at all highest value but group by another field, highest value of what column?

in your result you mention 4|3|n5, why not 5|3|n5 if you say Highest ?

ok so you edited it..

looks fairly simple something like

     select seqid, filed,name from table t1 join 
(select max(seqid) seqid from table group by fileid) as t2 on t2.seqid=t1.seqid

if seqid is PK you dont need anything but PK i dont get why would you want to join on 2 fields instead of just PK

Upvotes: 0

The Scrum Meister
The Scrum Meister

Reputation: 30111

SELECT seqid, fileid, name
FROM tbl JOIN (
    SELECT MAX(seqid) maxSeq, fileid fileid
    FROM tbl
    GROUP BY fileid
    ) tg ON tml.seqid = tg.maxSeq AND tbl.fileid = tg.fileid

Upvotes: 0

Adriaan Stander
Adriaan Stander

Reputation: 166346

How about something like

SELECT  t.*
FROM    Table t INNER JOIN
        (
            SELECT  fileid,
                    MAX(seqid) Maxseqid
            FROM    Table
            GROUP BY    fileid
        ) m ON  t.fileid = m.fileid
            AND t.seqid = m.Maxseqid

Upvotes: 2

Related Questions