CODEWITHSUNDEEP
mysql
Robert Benedetto
Robert Benedetto

Reputation: 1710

Get only 1 result per group of ID

I have a list of records (domestic_helper_idcards) and I want to return only one card per staff (domestic_helper_id) that is not deleted (is_deleted = 0), and that has the card_expiration_date furthest in the future (latest expiry date).

Have tried grouping and so on, but cant get it to work. Code below:

SELECT * FROM domestic_helper_idcard
where
is_deleted = 0
order by card_expiration_date desc

This returns the following (image):

enter image description here

I want only records with ID 4 and 5 to be returned. Anyone?

Upvotes: 0

Views: 40

Answers (1)

ScaisEdge
ScaisEdge

Reputation: 133360

You could use a join with the subquery grouped by domestic_helper_id with an aggregated function eg: max() 

SELECT d.* 
FROM domestic_helper_idcard d
inner join (
  select domestic_helper_id, max(id) max_id
  from domestic_helper_idcard
  where  is_deleted = 0
  group by  domestic_helper_id 

) t on t.domestic_helper_id = d.domestic_helper_id and t.max_id  = d.id

order by d.card_expiration_date desc

and as suggested by Jens after clarification using max card_expiration_date

SELECT d.* 
FROM domestic_helper_idcard d
inner join (
  select domestic_helper_id, max(card_expiration_date) max_date
  from domestic_helper_idcard
  where  is_deleted = 0
  group by  domestic_helper_id 

) t on t.domestic_helper_id = d.domestic_helper_id and t.max_date  = d.max_date

order by d.card_expiration_date desc

Upvotes: 1

Related Questions