How to select the recent date item

Question

I hava a table USER:

select t.* from user t;
uid  uname
1    tom
2    jim
3    bob
4    lily

and table TOYS

select t.* from toys t;
tid  uid  tdate
1    1    7/12/15
2    1    6/12/15
3    2    9/12/15
4    2    10/12/15
5    3    12/12/15

now I want

uid  tid   uname  tdate
1    2     tom    6/12/15
2    3     jim    9/12/15
3    5     bob    12/12/15
4          lily

what should I do? (I use oracle database);

do you want the toys with users sorted by date or the users with the most recent toy only? — Roman, Aug 24 '17 at 13:08

Roman · Answer 1 · 2017-08-24T13:22:09.350

0

most recent tdate per toy and user:

SELECT a.uid, b.tid, a.uname, MAX(b.tdate) AS tdate
FROM user a LEFT JOIN toys b ON a.uid = b.uid 
GROUP BY a.uid, b.tid, a.uname

or most recent toy per user (if any):

SELECT a.uid, b.tid, a.uname, MAX(b.tdate) 
FROM user a LEFT JOIN toys b ON a.uid = b.uid 
GROUP BY a.uid, a.uname

edited Aug 24 '17 at 13:22

answered Aug 24 '17 at 13:16

Roman

102
1
4

this sql can't execute – wkzq Aug 25 '17 at 03:52

score 0 · Accepted Answer · answered Aug 24 '17 at 13:16

0

You can generate a sequence for each user and pull back only the min date for each toy:

SELECT user.uid, toys.tid, user.name, toys.tdate 
  FROM    
      (SELECT tid, uid, tdate, 
      ROW_NUMBER() OVER (PARTITION BY uid ORDER BY tdate asc)
      AS SEQ
      FROM toys
     )table1
LEFT JOIN user on toys.uid = user.uid
WHERE SEQ = 1

answered Aug 24 '17 at 13:16

jimmy8ball

746
5
15

you think is right, but you are write incorrect。 – wkzq Aug 25 '17 at 03:42

How to select the recent date item

2 Answers2