LIMIT one row per ID (MySQL)

Question

I have joined a few tables in a MySQL query and they are linked by employee id number.

The ID number is unique in tables A and B but have multiple rows in table C with a date (stored as a string) being unique for each entry.

I need to identify the most recent date for each employee ID number in table C and LIMIT table C to one entry per ID number (the most recent obviously).

I am unsure how to set the LIMIT to 1 for each individual employee ID number.

Your help is appreciated.

[the manual to the rescue!](http://dev.mysql.com/doc/refman/5./en/example-maximum-column-group-row.html) (yes, I admit it's hard to find). — Wrikken, Mar 09 '13 at 21:14
I have checked the manual and did not see a way to declare one row per instance in another field. Im just wondering if theres a different function im missing here. — Kirk Logan, Mar 09 '13 at 21:25
Not in MySQL there isn't (there's one in PostreSQL I believe), really, the link I pointed you to has/is the answer (drop your `limit`, that's already guaranteed in other ways). — Wrikken, Mar 09 '13 at 21:28
I see, was the link pointing to a specific page? Because i get a page not found when i click on it. — Kirk Logan, Mar 09 '13 at 21:29
Ah, damn, it's broken indeed: [here's one I meant](http://dev.mysql.com/doc/refman/5.5/en/example-maximum-column-group-row.html), my apologies! — Wrikken, Mar 09 '13 at 21:31

score 2 · Answer 1 · answered Mar 09 '13 at 22:03

2

The query below uses a subquery to get each recent date for every EmployeeID on tableC.

SELECT  a.*, b.*, c.*
FROM    tableA a
        INNER JOIN tableB b 
            ON a.EmployeeID = b.EmployeeID
        INNER JOIN tableC c
            ON a.EmployeeID = c.EmployeeID
        INNER JOIN
        (
            SELECT  EmployeeID, MAX(date) max_date
            FROM    tableC
            GROUP   BY  EmployeeID
        ) d ON  c.EmployeeID = d.EmployeeID AND
                c.Date = d.max_date

answered Mar 09 '13 at 22:03

John Woo

258,903
69
498
492

Thanks for your response. After reading Wrikkens article link above the i was able to tack on a subquery in my WHERE clause. I used a LEFT JOIN as opposed to the INNER JOIN though. – Kirk Logan Mar 09 '13 at 23:19

score 0 · Answer 2 · answered Mar 09 '13 at 23:06

0

From what I understood on your question:

select a.*, b.*, max(c.date) from A
     inner join B on A.id=B.id
     inner join C on A.id=C.id
     group by A.id

This would group the records by the ID of A. That is, you would get a row in the response for each of the rows in A (because they are uniquely identified by that ID). Same thing happens with B, as they have the same ID. And then, in C, you have multiple records per record in A, so grouping them and getting the max of the date will just return one date of C per A (the max of them in each case).

answered Mar 09 '13 at 23:06

Alex Siri

2,856
1
19
24

I follow your logic here, however by declaring max(c.date) will that not return only the single largest date in the entire field? Or is that not the case since it is being grouped by a.id? I think ill test it either way. – Kirk Logan Mar 09 '13 at 23:22
It will return the largest date, and since you're grouping by A.id, will return the largest date for that specific A.id (which as I understand is what you are looking for) – Alex Siri Mar 10 '13 at 09:50

LIMIT one row per ID (MySQL)

2 Answers2