Select four items for each group?

Question

I have got two tables, first one called card:

| card_id | card_name |

And table withdrawal:

| withdrawal_id | card_id | transaction_date |

I want to get the last four transaction_dates for each card_id.

I tried this code but it wouldn't give the last 4 dates:

SELECT a.card_id, b.transaction_date
FROM card AS a 
JOIN withdrawal AS b ON a.card_id = b.card_id
GROUP BY a.card_id, b.transaction_date
ORDER BY a.card_id, b.transaction_date

What do I need to change?

possible duplicate of [Get top n records for each group of grouped results](http://stackoverflow.com/questions/12113699/get-top-n-records-for-each-group-of-grouped-results) — jpw, Nov 21 '14 at 20:11
This is a quite frequent question that has been answered many times before. These are some similar questions (& answers): https://stackoverflow.com/questions/tagged/greatest-n-per-group?sort=votes&pageSize=30 — jpw, Nov 21 '14 at 20:13

score 1 · Answer 1 · edited May 23 '17 at 11:57

1

It seems like you want to get the first N rows per group, which can be done using something like this:

SELECT a.card_id, b.transaction_date
FROM card a
JOIN withdrawal b ON a.card_id = b.card_id
WHERE(
   SELECT COUNT(*)
   FROM card c
   JOIN withdrawal w ON w.card_id = c.card_id
   WHERE c.card_id = a.card_id AND w.transaction_date <= b.transaction_date
) <= 4
ORDER BY a.card_id, b.transaction_date

See this question for more info on getting rows per group.

edited May 23 '17 at 11:57

Community

1
1

answered Nov 21 '14 at 20:15

AdamMc331

16,492
10
71
133

Glad I could help. Feel free to accept this answer if it worked. Also, bookmark this [article](http://www.xaprb.com/blog/2006/12/07/how-to-select-the-firstleastmax-row-per-group-in-sql/) if you ever have a problem like this again and are looking for different ways to do it. – AdamMc331 Nov 21 '14 at 20:22
@pdpandya I've notive that if you don't need anything else from the card table, just the card_id, you don't need to do a join since you can get the card_id from the withdrawal table. – AdamMc331 Nov 21 '14 at 20:44

score 0 · Answer 2 · answered Nov 21 '14 at 20:22

Here is another way of achieving it

select 
w.withdrawal_id,
w.card_id,
w.transaction_date
from card c
left join 
(
  select 
  r1.*
  from withdrawal r1
  where 
  (
    select count(*)
    from withdrawal r2
    where r1.card_id = r2.card_id
    AND r1.transaction_date <= r2.transaction_date
  ) <=4
  order by r1.transaction_date desc
)w
on w.card_id = c.card_id
order by c.card_id

demo

Select four items for each group?

2 Answers2