CODEWITHSUNDEEP
sqloracle-database
trueinViso
trueinViso

Reputation: 1394

Compare rows in SQL query

I need to compare rows in the same table of a query.

Here is an example of the table:

id    checkin     checkout    
1     01/15/13    01/31/13
1     01/31/13    05/20/13
2     01/15/13    05/20/13
3     01/15/13    01/19/13
3     01/19/13    05/20/13
4     01/15/13    02/22/13
5     01/15/13    03/01/13

I compare the checkout date to today's date, if it is before today's date then I want to return the result. However, similar to id's 1 and 3, they have multiple records. If one of the records associated with the same id have a record that has a checkout date after today's date then I don't want to return any of their records. I only want to return a record of each id where every record is before today's date in the checkout field.

Upvotes: 1

Views: 1190

Answers (3)

RGPT
RGPT

Reputation: 574

This should give you all the results of records that are before in time and that all with the same id are also before in time.

SELECT Ta.* 
 FROM TABLE Ta,
      (SELECT MAX(checkout) checkout, ID FROM TABLE GROUP BY ID) Tb
WHERE Ta.ID = Tb.ID 
  AND sysdate >= Ta.checkout -- checks for date in current record
  AND sysdate >= Tb.checkout -- checks for dates in all records

Upvotes: 0

dmg
dmg

Reputation: 4491

select id, checking  from
Table 
where checkout < CURRENT_DATE --Postgresql date of today, Oracle should have an equivalent now
and id not in (select id from Table where checkout >= CURRENT_DATE);

Upvotes: 0

Gordon Linoff
Gordon Linoff

Reputation: 1269463

For this purpose, analytic functions are the best approach:

select id, checkin, checkout
from (select t.*, max(checkout) over (partition by id) as maxco
      from t
     ) t
where maxco <= trunc(sysdate)

This assumes that the data is stored as date values and not as strings (otherwise, the max will return the wrong value).

Upvotes: 1

Related Questions