SQL Server : max value from sub query

Question

Please help me with retrieve max values. I've created a few simple tables. The first one is users the second one is books. So i need to use sub query to retrieve the the names of the books which date of taking by user is the latest

Here are the tables:

CREATE TABLE book_base 
(
    book_id int,
    user_id int,
    title VARCHAR(20),
    date DATE,
); 

CREATE TABLE users 
(
    userid int,
    name VARCHAR(20),
); 

INSERT INTO book_base (book_id, user_id, title, date)
VALUES ('221', 1, 'Just book', '2021-2-2'),
       ('222', 1, 'The book', '2020-4-8'),
       ('223', 1, 'My book', '2019-8-8'),
       ('228', 2, 'Nice book', '2021-1-2'),
       ('225', 2, 'Coole book', '2020-4-8'),
       ('226', 2, 'Super book', '2019-9-8');

INSERT INTO users (userid, name) 
VALUES ('1', 'John Smith'),
       ('2', 'Mary Adams');

And I've tried to do like this

SELECT
    userid AS [UID], 
    name AS [UserName], 
    bb.title, bb.date 
FROM
    users u
JOIN 
    (SELECT user_id title, MAX(date) 
     FROM book_base) bb ON u.userid = bb.user_id

The result should be just the rows there date is max date

Jarlh you mean like this? select userid as [UID], name as [UserName], bb.title, bb.date from users u JOIN (select user_id title, max(date) from book_base GROUP BY user_id) bb on u.userid=bb.user_id — Riggun, Dec 09 '21 at 11:23
Have you tried using simply Max(date) Over (Parititon by UserId Order By date desc) in select query — abdul qayyum, Dec 09 '21 at 11:28
Does this answer your question? [Get top 1 row of each group](https://stackoverflow.com/questions/6841605/get-top-1-row-of-each-group) — Charlieface, Dec 09 '21 at 14:01

score 0 · Answer 1 · answered Dec 09 '21 at 11:23

0

Try this, it's based on the data you privided:

SELECT *
FROM users u
JOIN (
  select user_id,MAX(date) as DATE 
  from book_base GROUP BY user_id
) bb ON u.userid = bb.user_id
JOIN book_base b ON u.userid = b.user_id
AND bb.date = b.date

answered Dec 09 '21 at 11:23

Dordi

778
1
5
14

Anton Grig · Answer 2 · 2021-12-09T11:33:19.813

0

You can use Cross Apply with a subquery that uses Top 1 With Ties.

Select u.userid As [UID], u.name As [UserName], bb.title, bb.date 
From users u Cross Apply 
    (Select Top 1 With Ties user_id, title, date 
     From book_base
     Where user_id=u.userid 
     Order by date Desc) As bb

edited Dec 09 '21 at 11:33

answered Dec 09 '21 at 11:26

Anton Grig

1,640
7
11

score 0 · Answer 3 · answered Dec 09 '21 at 11:33

0

Using a correlated subquery to filter by max date

select b1.*, u.name
from book_base b1
join users u on u.userid = b1.user_id
where date = (select max(b2.date) 
             from book_base b2 
             where b2.user_id = b1.user_id)

answered Dec 09 '21 at 11:33

Serg

22,285
5
21
48

score 0 · Answer 4 · answered Dec 09 '21 at 11:39

Select * from (
 SELECT
  userid AS [UID], 
  [name] AS [UserName], 
bb.title, Rank() OVER (Partition By bb.[user_id] order by bb.[date] desc) r,bb.[date]
FROM
users u inner join book_base bb ON u.userid = bb.[user_id]
) t
where r=1

Check if it helps,

If you remove outer select you will find why this work,

Rank is a built in function, it ranks all data by first partitioning it in groups and ordering as given then you can filter result as you need,

For example r=2 will provide second most recent date. and r=1 is top 1 record from each group

SQL Server : max value from sub query

4 Answers4