Compare rows in SQL query

Question

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.

Gordon Linoff · Accepted Answer · 2013-04-29 21:19:18Z

1

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).

answered Apr 29, 2013 at 21:19

Gordon Linoff

1.3m62 gold badges706 silver badges857 bronze badges

Sign up to request clarification or add additional context in comments.

2 Comments

trueinViso Over a year ago

I'm trying to use this with multiple tables because I am comparing the checkout date in a separate table. Do I just end finish the first from with: from(....)t1,t2 ?

Gordon Linoff Over a year ago

@trueinViso . . . I think you should ask another question, about multiple tables. Include table format, sample data, and the results that you want. This question is very clearly starts "I need to compare rows in the same table of a query".

dmg · Accepted Answer · 2013-04-29 21:19:38Z

0

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);

answered Apr 29, 2013 at 21:19

dmg

4,5892 gold badges21 silver badges27 bronze badges

Comments

RGPT · Accepted Answer · 2013-04-29 21:32:09Z

0

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

answered Apr 29, 2013 at 21:32

RGPT

5742 gold badges7 silver badges16 bronze badges

Collectives™ on Stack Overflow

Compare rows in SQL query

3 Answers 3

2 Comments

Comments

Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

3 Answers 3

2 Comments

Comments

Comments

Your Answer

Sign up or log in

Post as a guest

Related