CODEWITHSUNDEEP
mysql
Che
Che

Reputation: 15

order without duplicates

I have a table as follows:

ident_nr|proj_nr|start_time|
----------------------------
05.26.73|0000001|1116936920|
09.56.df|0000002|1213431234|
11.ac.12|0000003|1236485758|
98.er.df|0000001|1287789755|
70.12.5n|0000001|1011245554|
33.dt.vp|0000003|1239125544|

And I want a result like this:

ident_nr|proj_nr|start_time|
----------------------------
98.er.df|0000001|1287789755|
09.56.df|0000002|1213431234|
33.dt.vp|0000003|1239125544|

where proj_nr is in asc order and start time with the max value.

Note: ident_nr is unique and proj_nr could have multiple ident_nr. Database: MySQL.

Is there an SQL query that could achieve this result? Thanks

Upvotes: 0

Views: 134

Answers (2)

Joe Stefanelli
Joe Stefanelli

Reputation: 135818

SELECT t.ident_nr, t.proj_nr, t.start_time
    FROM YourTable t
        INNER JOIN (SELECT proj_nr, MAX(start_time) AS MaxTime
                        FROM YourTable
                        GROUP BY proj_nr) q
            ON t.proj_nr = q.proj_nr
                AND t.start_time = q.MaxTime
    ORDER BY t.proj_nr;

Upvotes: 1

Jacob
Jacob

Reputation: 43229

SELECT t1.* 
FROM table AS t1
INNER JOIN (
    SELECT proj_nr, MAX(start_time) AS MaxTime
    FROM table
    GROUP BY proj_nr) AS t2
    ON (t1.proj_nr = t2.proj_nr AND t1.start_time = t2.MaxTime)

Your criteria seems to be MAX(start_time) in your sample data. If not, please be more detailed in your question about what you want.

Upvotes: 1

Related Questions