SQL - Filtering by closest date

Question

Let's say I have a table:

SELECT * FROM table;

Ric     Charge     Date
VOD.L   2       20180601
VOD.L   5       20181002
VOD.L   4.5     20180212
RBS.L   3       20180504
RBS.L   6       20180708

How could I filter, by date, such that it will return ONLY the most recent charge E.g.

Ric     Charge     Date
VOD.L   4.5     20180212
RBS.L   6       20180708

Answer 1

I would prefer simple top 1

select top 1 * from t order by date desc

As you edit question, you can use co-related subquery byt your sample output is wrong

select * from t
where t.date in (select max(t1.date) from t t1
                                where t1.Ric=t.Ric
                 )

Demo

Ric     Charge  Date
VOD.L   5.000   02/10/2018 00:00:00
RBS.L   6.000   08/07/2018 00:00:

Answer 2

Something like

SELECT * FROM table WHERE date = (SELECT MAX(date) FROM table)

does what you ask(in multiple DB too)..

Date is a keyword in SQL and makes for a poor column name. If you can, change it now. If you can't, you might need to quote it according to the database you have

Answer 3

Order by the date and then use LIMIT 1 to only get one record.

SELECT *
       FROM table
       ORDER BY date DESC
       LIMIT 1;

Answer 4

A self left join with an inequality would work:

SELECT t1.*
FROM
 table t1
 LEFT JOIN table t2 ON
t1.date < t2.date
WHERE
t2.date IS NULL
GROUP BY
t1.Ric
;

Answer 5

I would use row_number() with ties clause :

select top (1) with ties t.*
from table t
order by row_number() over (partition by ric order by date desc);

If the date has ties then you can use dense_rank() instead of row_number().

Answer 6

You can use a window function such as ROW_NUMBER to create a windowed order number, then you can extract only the record with row number = 1 (the latest date):

declare @tmp table (Ric varchar(50), Charge numeric(10,3), [Date] Date)
insert into @tmp values
 ('VOD.L', 2  , '20180601')
,('VOD.L', 5  , '20181002')
,('VOD.L', 4.5, '20180212')
,('RBS.L', 3  , '20180504')
,('RBS.L', 6  , '20180708')

select t.Ric, t.Charge, t.[Date] 
from (
    select ric, 
        Charge,
        row_number() over (partition by ric order by [Date] desc) as rn,
        [Date]
    from @tmp
 ) t where rn = 1

Result:

From your question it is not clear what sholud happen when there are more rows with the same Ric/date values

Answer 7

As an alternative, you could use not exists.

SELECT * 
FROM TableA AS A
WHERE NOT EXISTS ( SELECT * FROM TableA AS A2
                   WHERE A.Ric = A2.Ric
                     AND A2.Date > A.Date )