Select with left join where we need the row with the oldest value for a column

Question

Imagine the following scenario where we have two tables. One table stores our players with their status, creation date and id and another table keeps their login activity.

I want to retrieve all the players which logged in more than N years ago or those who never logged in. As you can see we have a player with id = 4 who never logged into the game so we also want to include him in our list by checking his creation date.

It is also possible to have multiple entries for a player in the Activity Table so our interest is to bring only the row which has a [max(LoginDate) < N] for that player.

I already have a solution but I am eager to see different approaches.

PLAYER_TABLE
--------------------------------------
|PlayerId |CreationDate | Status     |
--------------------------------------
|1        |01.01.2000     | ACTIVE   |
--------------------------------------
|2        |01.01.2019     | ACTIVE   |          
--------------------------------------
|3        |01.01.2001     | SUSPENDED|          
--------------------------------------
|4        |01.01.2004     | ACTIVE   |          
--------------------------------------

ACTIVITY_TABLE
-----------------------
|AccountId |LoginDate  |
-----------------------
|1        |01.01.2005 |
-----------------------
|1        |05.06.2007 |          
-----------------------
|2        |03.05.2010 |         
-----------------------
|3        |01.01.2018 |         
-----------------------

This is a faq. It has a tag: [tag:greatest-n-per-group]. I would google your title but the automated 'Related' links on this page include [Select first row in each GROUP BY group?](https://stackoverflow.com/questions/3800551/select-first-row-in-each-group-by-group?rq=1) with over 1000 votes & others including another over 1000 votes. Before considering posting please always google many clear, concise & precise phrasings of your question/problem/goal, with & without your particular strings/names, & read many answers. If you post a question, use one phrasing as title. — philipxy, Mar 12 '19 at 07:06
PS Moreover those links would have been offered to you when you were composing your question. Also please show what you are able to do. Give a [mcve] with code questions. Dumps of specifications are not on-topic questions. — philipxy, Mar 12 '19 at 07:13

xQbert · Answer 1 · 2018-10-01T17:24:02.683

Using a CTE (Common table expression) to materialize the analytic and let us filter based on a single date:

WITH CTE AS(
SELECT player.PlayerId
     , activity.ddt 
     , coalesce(max(Activity.Logindate) over (partition by Activity.AccountID), player.CreationDate) as  LastLoginOrCreation
FROM PLAYER_TABLE player
LEFT JOIN  ACTIVITY_TABLE activity
  ON activity.AccountId = player.PlayerId)

SELECT * 
FROM CTE 
WHERE player.status != 'SUSPENDED'
  and LastLoginOrCreation <= to_date('2015-01-01', 'yyyy-MM-dd')

Avoid the CTE eliminate the inline select but still have to filter on two dates.

SELECT player.PlayerId
     , activity.ddt 
     , coalesce(max(Activity.Logindate) over (partition by Activity.AccountID), player.CreationDate) as  LastLoginOrCreation
FROM PLAYER_TABLE player
LEFT JOIN  ACTIVITY_TABLE activity
  ON activity.AccountId = player.PlayerId
WHERE (player.CREATION_DATE <= to_date('2015-01-01', 'yyyy-MM-dd') 
   OR activity.LoginDate <= to_date('2015-01-01', 'yyyy-MM-dd'))
  AND player.status != 'SUSPENDED';

Tested your first solution bit you get something like: 1 01.01.2005, 1 01.01.2005, 2 01.01.2018, 2 01.01.2018; with lots of duplicates — Lucian, Oct 17 '18 at 12:51

Select with left join where we need the row with the oldest value for a column

1 Answers1