SQL Query - Get the latest rates

Question

My Table:

Date              Code        Rate
----              ----        ----
2015-01-01          1         100
2015-01-01          2         200
2015-01-01          3         300
2015-06-01          1         150

How can i get the latest rates(By Date), like:

Date              Code        Rate
----              ----        ----
2015-01-01          2         200
2015-01-01          3         300
2015-06-01          1         150

SQL Query?

Take a look at `ROW_NUMBER` – Giorgos Betsos Feb 01 '16 at 08:38 — Giorgos Betsos, Feb 01 '16 at 08:38
use `group by Code` with `select max(Rate) as Rate` – krtek Feb 01 '16 at 08:42 — krtek, Feb 01 '16 at 08:42

score 1 · Answer 1 · answered Feb 01 '16 at 08:39

1

WITH cte AS (
    SELECT 
        *,
        ROW_NUMBER() OVER (PARTITION BY Code ORDER BY Date DESC) rn
    FROM yourtable
)
SELECT 
        Date, 
        Code, 
        Rate
FROM cte 
WHERE rn=1

answered Feb 01 '16 at 08:39

Dzmitry Paliakou

1,587
19
27

inverted_index · Answer 2 · 2016-02-01T14:16:37.247

0

You can do it with sub-query Try this:

SELECT * FROM (
    SELECT * FROM your_table ORDER BY date DESC LIMIT 5
) sub
ORDER BY date ASC

This code gets the last 5 rows from your table.

edited Feb 01 '16 at 14:16

answered Feb 01 '16 at 08:40

inverted_index

2,329
21
40

score 0 · Answer 3 · edited May 04 '23 at 17:46

0

Try this:

select  Date,
        Code,
        Rate
from    (
            select  ROW_NUMBER() over(partition by    code order by   date desc)as Id,date,code,rate
            from    yourTable
        ) x
where   Id = 1
order by    date

edited May 04 '23 at 17:46

General Grievance

4,555
31
31
45

answered Feb 01 '16 at 08:45

JassyJov

204
1
9

SQL Query - Get the latest rates

3 Answers3