Request a query to compare timestamp

jl66
jl66 used Ask the Experts™
on
Have a table T with the columns/records
GROUP    NAME    T1         T2
2             BB        7/1/11    7/3/11
2             SS        7/5/11    7/6/11
3             SS        7/1/11    7/3/11
3             CC        7/5/11    7/6/11
3             DD        7/4/11    7/6/11
4             SS        8/1/11    8/2/11
4             SS        8/3/11    8/3/11
....
Select the records in each group like 2, 3,... if Name =SS and T1 > T2 (here T2 comes from any other record in the same group). For example,

2       SS        7/5/11    7/6/11   -- selected because its T1=7/5/11 > T2=7/3/11 (other record in the same group)

However, none of records are selected from group =3.

Similarly, the following record is selected
4       SS        8/3/11    8/3/11

I tried to use " over partition by ...". Just missed something.  I will greatly appreciate any guru's advice/queries.
Comment
Watch Question

Do more with

Expert Office
EXPERT OFFICE® is a registered trademark of EXPERTS EXCHANGE®
I currently don't have a way to test your scenario, but try something like this:

SELECT "GROUP", NAME, t1, t2
FROM
(
SELECT "GROUP", NAME, t1, t2,
CASE WHEN t2 > lead(t2) OVER(PARTITION BY "GROUP" ORDER BY t2 DESC) AND NAME = 'SS' THEN 1 ELSE 0 END row_selected
FROM <your table>
)
WHERE row_selected = 1
/

Most Valuable Expert 2011
Top Expert 2012
Commented:
SELECT groupid,
       name,
       t1,
       t2
  FROM (SELECT groupid,
               name,
               t1,
               t2,
               MAX(t1) OVER (PARTITION BY groupid) mt1,
               MIN(t2) OVER (PARTITION BY groupid) mt2
          FROM yourtable)
 WHERE name = 'SS' AND mt1 > mt2
jl66Consultant

Author

Commented:
Thank both of you very much. Excellent!!
sdstuber: your answer just missed a bit, which rarely happened.
Most Valuable Expert 2011
Top Expert 2012

Commented:
hmmm, I guess I misunderstood the question then.   I tested my query and it did exactly what I thought you asked for.

Do more with

Expert Office
Submit tech questions to Ask the Experts™ at any time to receive solutions, advice, and new ideas from leading industry professionals.

Start 7-Day Free Trial