SQL query to find most recent date using two tables

Question

I have two tables in a database. One called Users and one called Days. The Users table has a column called ID which contains some numbers and an ActiveUser column which contains either a 0(not active) or 1(active). The Days table has a column called UserID which contains the same numbers the ID column from the Users table has. It also has a column called Day which contains info like "2015-01-01 00:00:00:000".

Users Table:

ID   |   ActiveUser
-------------------
10   |   0
11   |   1
12   |   1
13   |   0

Days Table:

User_ID   |   Day
------------------
10        |   2010-06-24 00:00:00.000   
11        |   2011-07-05 00:00:00.000
12        |   2008-06-19 00:00:00.000
13        |   2010-06-20 00:00:00.000
10        |   2009-09-02 00:00:00.000
12        |   2010-08-15 00:00:00.000
11        |   2011-05-06 00:00:00.000
13        |   2012-04-25 00:00:00.000

I'm trying to create a query that finds the most recent Day listed for each Active user. So using the tables above, the query I'm trying to make should give me the following:

Day
------
2011-07-05 00:00:00.000
2010-08-15 00:00:00.000

Which corresponds to the two Active users with user ID's 11 and 12, both of which have two entries for Day, but the query picks the most recent date.

I'm new to SQL, and the closest I've got is below, which doesn't take into account the Users table (also, used https://stackoverflow.com/a/2411703/2480598 as a template):

select Days.Day
from Days
inner join (select User_ID, max(day) as MaxDay
   from Days
   group by User_ID
) tm on Days.User_ID = tm.User_ID and Days.Day = tm.MaxDay

This gives me the list of dates for users active and non-active.

Microsoft SQL Server. Updated the tags to reflect this, sorry about that. Title was wrong as well. — pfinferno, Nov 30 '15 at 18:27

APH · Answer 1 · 2015-11-30T20:54:42.087

2

Select a.User_ID, max(a.day) as LatestDay from Days a
Inner Join Users b
on a.User_ID = b.ID and b.ActiveUser = 1
group by a.User_ID

The inner join limits it to active users only. You can remove a.User_ID from the select if you only want a list of days.

edited Nov 30 '15 at 20:54

answered Nov 30 '15 at 18:31

APH

4,109
1
25
36

Tried this. I keep getting an error saying "Invalid column name 'Day'" which certainly isn't an invalid column. – pfinferno Nov 30 '15 at 18:51
2

This shoud be `a.day` – Giorgi Nakeuri Nov 30 '15 at 18:54
Thanks @GiorgiNakeuri - fixed. – APH Nov 30 '15 at 20:54

Gordon Linoff · Answer 2 · 2015-11-30T19:26:02.893

1

You need to join to the users table to get the active users. Here is a simple way:

select u.id, max(d.day) as MaxDay
from Days d join
     Users u
     on d.user_id = u.id
where u.ActiveUser = 1
group by u.User_ID;

edited Nov 30 '15 at 19:26

answered Nov 30 '15 at 18:32

Gordon Linoff

1,242,037
58
646
786

Hmm, I'm getting a syntax error near the "where" on line 6. I changed the names and all to match my tables but still the same problem. – pfinferno Nov 30 '15 at 18:49
1

@Gordon, has a little bug. Just place `where` clause before `order by` clause. – Giorgi Nakeuri Nov 30 '15 at 18:55

Giorgi Nakeuri · Accepted Answer · 2015-11-30T18:48:56.370

1

This is classic example where APPLY can be applied:

select * from users u
outer apply(select top 1 day from days where userid = u.id order by day desc)a
where u.ActiveUser = 1

If you want only the day column(I think it makes no sense):

select max(d) as day
from users u
join days d on d.userid = u.id
where u.ActiveUser = 1
group by u.id

edited Nov 30 '15 at 18:48

answered Nov 30 '15 at 18:36

Giorgi Nakeuri

35,155
8
47
75

This works, I'll have to look more into what apply does. The reason I just want the day column is for some code I have in C# just using the dates for certain tasks. – pfinferno Nov 30 '15 at 19:00

SQL query to find most recent date using two tables

3 Answers3