I don't find the sql request

Question

Hi everybody,

Here it's my problem I've a list of the following measure :

src1   dst2   24th december 2009
src1   dst3   22th december 2009
src1   dst2   18th december 2009

I would like to have just the latest measures with a sql request -> 2 first lines in my case because the pairs(src and dst) aren't the same.

I try to use DISTINCT but I have just the 2 first columns and I will all columns. I try too GROUP BY but I hadn't success.

Anyone can help me ?

Thx

Narglix

Answer 1

select src, dst, max(date) as Date
from MyTable
group by src, dst

Update:

select *
from MyTable t
inner join (
    select src, dst, max(date) as Date 
    from MyTable 
    group by src, dst 
) tm on t.src = tm.src and t.dst = tm.dst and t.Date = tm.Date

Answer 2

or you can use distinct on

select distinct on (src,dst) 
       src,dst,date
  from mytable
 order by src,dst,date desc;

Answer 3

In 8.4 you can also do:

select src, dst, date
from (select row_number() over(partition by src, dst order by date desc) as row,
             src, dst, date
      from t) x
where x.row = 1

This uses only one table scan, and also generalises to be being able to take the 5 latest measures etc.