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,472
16
23

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,211
17
35

While this link may answer the question, it is better to include the essential parts of the answer here and provide the link for reference. Link-only answers can become invalid if the linked page changes. – greg-449 Feb 01 '16 at 10:22
Thanks for ur comment. I will update in a minute :) @greg-449 – inverted_index Feb 01 '16 at 14:12

score 0 · Answer 3 · answered Feb 01 '16 at 08:45

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

answered Feb 01 '16 at 08:45

JassyJov

180
7

SQL Query - Get the latest rates

3 Answers3