0

I need to get the row with latest date in my table. I have tried using where rownum = 1 as well as using max function but I dont seem to get the correct result.

This is what I have tried, this gives me more than one rows, I want the row with the max date:

SELECT * FROM (SELECT col1,col2,coldate3,max(colDate3) 
over (partition by coldate3) max_date FROM MY_TABLE 
WHERE 
TRIM(col1) = TRIM('abc') 
AND TRIM(col2) = TRIM('efg') )
where coldate3= max_date;

I have also tried this, this gives me the max date but value of col1 is of some other row:

 SELECT MY_TABLE.TARIFF 
FROM MY_TABLE 
WHERE ROWNUM = 1 
AND TRIM(MY_TABLE.col1) = TRIM(:ls_col1) 
AND TRIM(MY_TABLE.col2) = TRIM(:ls_col2) 
ORDER BY coldate3 desc

What am I doing wrong.

Sachu
  • 7,555
  • 7
  • 55
  • 94
zeppelin
  • 451
  • 1
  • 4
  • 24
  • Looks incredibly convoluted to me... Not an Oracle user, but you should be able to do something like `select * from my_table order by coldate3 desc limit 1` (ie, sort on coldate3 descending and online take the row with the highest value). – fvu Jun 01 '15 at 11:53
  • 1
    Okay, in Oracle (where LIMIT-alike constructs don't seem to exist) that should look +- like this: `select * from (select * from my_table order by coldate3 desc ) where ROWNUM < 2 ` – fvu Jun 01 '15 at 11:57
  • Thanks fvu, this was so simple I was applying group by and max functions on it. Thanks. Post it as an answer I will accept it. – zeppelin Jun 01 '15 at 12:00
  • If this works it's actually a duplicate, as @Ben rightfully decided seconds ago ;) – fvu Jun 01 '15 at 12:01
  • @a_horse_with_no_name ha, I just replaced :ls_col1 with abc just for clarity. I am not using it in my actual query. It would be parameterized. – zeppelin Jun 01 '15 at 12:01
  • I did not know that. – zeppelin Jun 01 '15 at 12:01
  • @LukasEder: when I added my comment, the `TRIM(:ls_col1)` wasn't there. –  Jun 01 '15 at 12:03

0 Answers0