CODEWITHSUNDEEP
sqloracle-database
Zaiman Noris
Zaiman Noris

Reputation: 327

Find records with Max & Min Datestamp to find overlapped Period

I have these rows in my Oracle 9i DB. These rows already filtered by 'group' column.

ticket_id    | datestamp            | type    | group
1-7323887901 | 22-Apr-14 3:38:14 PM | create  | A
1-7323889625 | 22-Apr-14 3:43:39 PM | create  | A
1-7323889625 | 05-May-14 5:38:37 PM | resolve | A
1-7323889628 | 05-May-14 5:38:37 PM | create  | B
1-7323887901 | 05-May-14 6:13:29 PM | resolve | A

I would like to create a single query to find ticket ID with max created datestamp & ticket ID with min resolved datestamp.

Basically my excepted output as below. (always expecting two rows returned)

ticket_id    | datestamp            | type    | group
1-7323889625 | 22-Apr-14 3:43:39 PM | create  | A
1-7323889625 | 05-May-14 5:38:37 PM | resolve | A

Thx.

[edit] added another record from another group which i'd like to exclude

Upvotes: 0

Views: 285

Answers (5)

AndreDuarte
AndreDuarte

Reputation: 804

Try this:

select t.id from ticket t
 where datestamp = (select max(datestamp) from ticket where type = 'create')
    or datestamp = (select min(datestamp) from ticket where type = 'resolve')

This will not work if you have 2 datestamps that are exactly the same. You could use rownum, but this is not a good idea.

select t.id from ticket t
 where datestamp = (select max(datestamp) from ticket where type = 'create' and rownum =1)
    or datestamp = (select min(datestamp) from ticket where type = 'resolve' and rownum =1)

Upvotes: 0

Sun21
Sun21

Reputation: 34

SELECT * FROM Tablename
WHERE (date_stamp = (SELECT MAX(date_stamp) FROM Tablename WHERE TYPE='CREATE')
OR
date_stamp = (SELECT MIN(date_stamp) FROM Tablename WHERE TYPE='RESOLVE'))

Upvotes: 0

user3278460
user3278460

Reputation: 

select * from (
select *
from ticket 
where type = 'create'
order by datestamp desc
) where rownum = 1

union all

select * from (
select *
from ticket 
where type = 'resolved'
order by datestamp 
) where rownum = 1

Upvotes: 1

Dan Bracuk
Dan Bracuk

Reputation: 20794

You can join to subqueries. Something like this will get you started

select ticketid, type, mindate thedate
from ticket join 
(select min(datestamp) mindate
from ticket
where type = 'create' ) t1 on ticket.datestamp = t1.datestamp
where type = 'create'
union

select ticketid, type, mindate thedate
from ticket join 
(select min(datestamp) mindate
from ticket
where type = 'resolve' ) t2 on ticket.datestamp = t2.datestamp
where type = 'resolve'

If you have ties, you have to decide what you want before an attempt to write code to handle it takes place.

Upvotes: 0

akzhere
akzhere

Reputation: 353

You can simply use two differetn select queries one with MAX() aggregate function and other with MIN() aggregate function and then combining the result of two by UNION operator.

Or try this,

SELECT * FROM table_name 
 WHERE datestamp = (SELECT MAX(datestamp) FROM table_name)
 OR
 WHERE datestamp = (SELECT MIN(datestamp) FROM table_name);

Hope this helps.

Upvotes: 0

Related Questions