simple sql (i hope!)

Question

Given a data set similar to this:

ccy | time | fxrate
GBP | 11   | 1.2
EUR | 21   | 1.4
CHF | 9    | 3.1
GBP | 15   | 1.1
EUR | 20   | 1.5
CHF | 1    | 3.0
CHF | 7    | 3.0
GBP | 20   | 1.9

I want to get the latest fxrates (by 'time') for each ccy:

ccy | time | fxrate
GBP | 20   | 1.9
EUR | 21   | 1.4
CHF | 9    | 3.1

Is it possible to get this data with a single sql query? My skills are failing me. I assume I need to GROUP BY ccy..? where max(time) ..? LIMIT 1 ..? Help!

[EDIT] using postgresql

Possible duplicate of [SQL select only rows with max value on a column](https://stackoverflow.com/questions/7745609/sql-select-only-rows-with-max-value-on-a-column) — Nick, Apr 08 '19 at 13:21
@jarlh thanks - i was just trying to simplify the dataset as much as possible. Maybe too much. — pomo, Apr 08 '19 at 13:22

Yogesh Sharma · Answer 1 · 2019-04-08T13:34:21.280

4

Yes you can use group by for only max fxrate but in your case you need a correlated subquery :

select t.*
from table t
where t.fxrate = (select max(t1.fxrate) from table t1 where t1.ccy = t.ccy);

In postgresql you can do :

select distinct on (ccy) *
from table t
order by t.fxrate desc;

edited Apr 08 '19 at 13:34

answered Apr 08 '19 at 13:20

Yogesh Sharma

49,870
5
26
52

It works (the 1st version) but I'm struggling to prevent it from doing a full table scan so it's slow. Playing with the indexes now. – pomo Apr 08 '19 at 14:26
1

@pomo Try the second query as it's a much better way than the first, but change the order by to `order by ccy, time desc` - that should be both correct and fast. – 404 Apr 08 '19 at 15:05
Thanks. That seems to work. It's slow though. It's doing a full table scan. Guess I'm missing an index. – pomo May 09 '19 at 12:44

score 2 · Answer 2 · answered Apr 08 '19 at 13:24

2

row_number can be helpful

select * from (select *,row_number()over(partition by ccy order by time desc) rn
from table_name
) a where a.rn=1

answered Apr 08 '19 at 13:24

Zaynul Abadin Tuhin

31,407
5
33
63

Thanks, but it's incredibly slow. It's doing a full table scan even though I (think) I have the correct indexes in place. – pomo Apr 08 '19 at 14:43

score 2 · Answer 3 · answered Apr 08 '19 at 13:30

2

You could also use an inner join on sub query for max time group by ccy

select m.ccy, m.time, m.fxrate 
from my_table  m
inner join  (
  select ccy, max(time) max_time  
  from my_table  
  group by ccy
) t on t.ccy = m.ccy and t.max_time = m.time

answered Apr 08 '19 at 13:30

ScaisEdge

131,976
10
91
107

score 1 · Answer 4 · answered Apr 08 '19 at 13:27

1

select ccy , time ,fxrate 
from mytable
Inner join(SELECT max(time) maxtime, ccy
            FROM  mytable
            group by ccy ) MAXCCY on maxccy.maxtime = mytable.time and MAXCCY.ccy = mytable.ccy

answered Apr 08 '19 at 13:27

Rima

1,447
1
6
12

simple sql (i hope!)

4 Answers4