CODEWITHSUNDEEP
mysqlsql
Eric Mathison
Eric Mathison

Reputation: 1256

Select each row of table except where the id is not the maximum value for a given foreign key

Given a table such as the following called form_letters:

+---------------+----+
| respondent_id | id |
+---------------+----+
|             3 |  1 |
|             7 |  2 |
|             7 |  3 |
+---------------+----+

How can I select each of these rows except the ones that do not have the maximum id value for a given respondent_id.

Example results:

+---------------+----+
| respondent_id | id |
+---------------+----+
|             3 |  1 |
|             7 |  3 |
+---------------+----+

Upvotes: 0

Views: 67

Answers (3)

Tyler Kent
Tyler Kent

Reputation: 33

Here's how I would do it. Get the max id in a sub query, then join it back to your original table. Next, limit to records where the ID does not equal the max id.

Edit: Opposite of this. limit to records where the ID = MaxID. Code changed below.

Select    FL.Respondent_ID, FL.ID, A.Max_ID
From      Form_Letters FL
left join (
   select    Respondent_ID, Max(ID) as Max_ID
   from      Form_Letters
   group by  Respondent_ID) A
on  FL.Respondent_ID = A.Respondent_ID
where FL.ID = A.Max_ID

Upvotes: 0

Abhik Chakraborty
Abhik Chakraborty

Reputation: 44864

There are many ways of doing it. group by using max(), or using not exits and using left join

Here is using left join which is better in terms of performance on indexed columns

select 
f1.*
from form_letters f1
left join form_letters f2 on f1.respondent_id = f2.respondent_id
and f1.id < f2.id
where f2.respondent_id is null

Using not exits

select f1.*
from form_letters f1
where not exists
(
  select 1 from form_letters f2
  where f1.respondent_id = f2.respondent_id
  and f1.id < f2.id
)

Demo

Upvotes: 1

Darren Taylor
Darren Taylor

Reputation: 2025

Something like this should work;

SELECT respondent_id, MAX(id) as id FROM form_letters
group by respondent_id

MySQL fiddle:

http://sqlfiddle.com/#!2/5c4dc0/2

Upvotes: 2

Related Questions