Home sql fetch one record per date from table
Reply: 2

sql fetch one record per date from table

Kuldeep Thakur
1#
Kuldeep Thakur Published in 2017-12-07 11:43:26Z

I have a table like:

o_id  order_no   date        etc....
 4     1         2017-02-01
 4     2         2017-02-01
 4     3         2017-02-01

what i want is only one(highest order no) record per date should be fetched. For example for only order_no 3 for o_id 4 should be fetched

Output:

o_id       order_no      date
4          3             2017-02-01
2          1             .........so on
Gordon Linoff
2#
Gordon Linoff Reply to 2017-12-07 11:46:36Z

The canonical method is to use the ANSI-standard ROW_NUMBER() function:

select t.*
from (select t.*,
             row_number() over (partition by o_id order by order_no desc) as seqnum
      from t
     ) t
where seqnum = 1;
Radim Bača
3#
Radim Bača Reply to 2017-12-07 12:22:30Z

You may use GROUP BY and MAX if the order_no is unique in each o_id group

select your_table.*
from your_table
join
(
  select o_id, max(order_no) maxorder
  from your_table
  group by o_id
) t on t.o_id = your_table.oid and t.maxorder = your_table.order_no

Similar questions

Select corresponding to row from the same table - you can find there a performance comparison of group by and window function version, however, the comparison is for SQL Server.

You need to login account before you can post.

About| Privacy statement| Terms of Service| Advertising| Contact us| Help| Sitemap|
Processed in 0.382958 second(s) , Gzip On .

© 2016 Powered by mzan.com design MATCHINFO