-1

So here is a case where one of the column in a table has same values for multiple records but the Date column has incremental values. 

ID| PAY_CODE| RATE  |   Date
---------------------------------
1 |  1111   |  50   | 2017-01-01
2 |  1111   |  50   | 2017-02-01
3 |  1111   |  50   | 2017-03-11
4 |  1111   |  50   | 2017-05-21

How can I print only the latest record if the latest Date entered is unknown?

Expected result:

ID| PAY_CODE| RATE  |   Date
---------------------------------
4 |  1111   |  50   | 2017-05-21

I need a SQL query which internally compares the Date column and returns the highest value (Latest date in this case) - is this even possible?

Mureinik
  • 297,002
  • 52
  • 306
  • 350
intruder
  • 417
  • 1
  • 3
  • 18

4 Answers4

2

I think this does what you want:

select t.*
from t
where t.date = (select max(t2.date) from t t2);
Gordon Linoff
  • 1,242,037
  • 58
  • 646
  • 786
2

You could use the rank window function:

SELECT id, pay_code, rate, date
FROM   (SELECT id, pay_code, rate, date, RANK() OVER (ORDER BY data DESC) AS rk
        FROM   mytable) t
WHERE  rk = 1
Mureinik
  • 297,002
  • 52
  • 306
  • 350
  • This will return multiple rows if they are tied for the latest date; using `ROW_NUMBER` instead of `RANK` will only return a single row. – MT0 Oct 05 '17 at 20:27
  • @MT0 OP requested the rows from the latest date. As far as I understand it, allowing multiple results is the intended behavior. If the requirement is indeed to just return one of these rows, `row_number` will indeed do the trick, – Mureinik Oct 05 '17 at 20:29
  • The OP requested "How can I print only the latest record" but, regardless of what they intend, either `RANK` or `ROW_NUMBER` will solve it. – MT0 Oct 05 '17 at 20:30
0
select t.*
from ( select * from table order by date desc) t
where rownum < 2
Ted at ORCL.Pro
  • 1,602
  • 1
  • 7
  • 10
0

I think you should use SELECT * FROM YOUR_TABLE T WHERE T.DATE = ( SELECT MAX(DATE) FROM YOUR_TABLE) this way you ensure you grab the latest result, other solutions using TOP could not work as expected

Ringuerel
  • 122
  • 4