0

I am looking to get the max value, but when I have several rows. something similar to this post, but for DB2:

This is what i'd like to accomplish: I have a table with orders a client made and would like to select the last order according to the highest order date. 

client  order date    ord number
111     2013-01-01    asdf      |
222     2013-01-05    kjhg      |
111     2013-02-02    uiop      |
222     2013-02-06    fghj      |

result 

client  order date
111     2013-02-02
222     2013-02-06

I wish I was able to post an image, but it wont allow me...

Thank you!

Community
  • 1
  • 1
OGERWOLF
  • 117
  • 4
  • 13

1 Answers1

1

Do you just want a group by?

select client, max(OrderDate) as OrderDate
from orders o
group by client;

If you want the complete row, there are several ways. One way is with row_number():

select o.*
from (select o.*, row_number() over (partition by client order by orderdate desc) as seqnum
      from orders o
     ) o
where seqnum = 1;
Gordon Linoff
  • 1,242,037
  • 58
  • 646
  • 786
  • Thank you for the reply. 2nd answer is a bit complicated for me, not sure I can test it. For the 1st one, it works only if I have 1 table. When linking 2 or more tables with sometime duplicated in first or 2nd column (ex, additional tables has 2 or more lines for each order), this solution doesn't work. – OGERWOLF Sep 18 '14 at 17:16
  • @OGERWOLF . . . Your question only mentions one table. If you have additional tables, you should ask another question. – Gordon Linoff Sep 18 '14 at 17:32
  • Actually, I have 3-4 tables...depends on the query. It is just that I need to have those linked in order to have my result However, I do not necessarily have them in the select. Should I create a new question? Thanks – OGERWOLF Sep 18 '14 at 17:37
  • @OGERWOLF . . . This question is: "I have a table with orders a client made and would like to select the last order according to the highest order date.". If you have another question, then ask another question. – Gordon Linoff Sep 18 '14 at 17:44