CODEWITHSUNDEEP
sqlms-accessinformix
cedarsspirit
cedarsspirit

Reputation: 11

Select most recent N records for each user - INFORMIX DB

I have a table with the following fields:

USER_ID
SITE_ID
STATION_ID
Login_DateTime

I would like to pull out the most recent N (let's say 5) records for each User/Site combination.

Can you please help me to create the query for MS Access and Informix DBs?

Upvotes: 1

Views: 773

Answers (1)

Gordon Linoff
Gordon Linoff

Reputation: 1270873

I think the only way to do this using SQL that is compatible with both Informix and MS Access is to use a correlated subquery:

select t.*
from table as t
where (select count(*)
       from table as t2
       where t2.user_id = t.user_id and t2.site_id = t.site_id and
             t2.login_datetime >= t.login_datetime
      ) <= 5;

Upvotes: 1

Related Questions