CODEWITHSUNDEEP
oracle-database
Skulmuk
Skulmuk

Reputation: 605

Oracle: Returning the earliest date

I have a stored procedure which I use to return the earliest available date in a column of dates. I need to only return the earliest, and currently am using date arithmetic to reduce the number of returned rows. However, doing it this way, my procedure gets stuck in a loop of the first two top returned values, meaning I have several rows which are never read. Could somebody please let me know where I need to use the MIN function in the following WHERE clause, please? Thanks:

SELECT        **COLS**                
INTO          **VARS**
FROM          **TABLE**

INNER JOIN    **TABLE TO JOIN**
ON            **JOIN TARGET**

WHERE         ROWNUM = 1 AND LASTREADTIME < SYSDATE - (30/86400) 

ORDER BY      LASTREADTIME DESC;

Upvotes: 0

Views: 13070

Answers (1)

Rapha&#235;l Althaus
Rapha&#235;l Althaus

Reputation: 60503

if you only need the earliest date

SELECT MIN(LastReadTime)
INTO  **VARS**
FROM table

if you need other datas

SELECT t2.col1, t1.col1, t1.col2, t1.LastTreadTime
INTO **VARS**
FROM table t1
JOIN table2 t2 on t1.col1 = t2.col1
WHERE t1.LastReadTime = (SELECT MIN(t2.LastReadTime) FROM table t2);

Upvotes: 3

Related Questions