SQL query [message #597110] |
Tue, 01 October 2013 02:57 |
panyamravi
Messages: 11 Registered: February 2009
|
Junior Member |
|
|
Hi All,
I have the below scenario:
[u]Report_Time M I Ta[/u]
02-SEP-13 X Y 35167005
01-SEP-13 X Y 35931902
03-SEP-13 X Y 35931901
The output I am expecting from above is:
X Y 01-SEP-13 03-SEP-2013 35931902 35931901
In words:
I need to extract minimum report time , max report time and corresponding Ta.
I tried with analytical functions but no luck.
Any help on this please?
[Updated on: Tue, 01 October 2013 02:59] Report message to a moderator
|
|
|
|
|
Re: SQL query [message #597117 is a reply to message #597110] |
Tue, 01 October 2013 03:59 |
|
Michel Cadot
Messages: 68647 Registered: March 2007 Location: Nanterre, France, http://...
|
Senior Member Account Moderator |
|
|
Another way:
with
data as (
select report_time, m, i ta,
rank() over (order by report_time) rk1,
rank() over (order by report_time desc) rk2
from myTab
)
select report_time, m, i ta
from data
where rk1=1 or rk2=1
/
[Updated on: Tue, 01 October 2013 04:00] Report message to a moderator
|
|
|
|
|
Re: SQL query [message #597125 is a reply to message #597122] |
Tue, 01 October 2013 04:30 |
cookiemonster
Messages: 13920 Registered: September 2008 Location: Rainy Manchester
|
Senior Member |
|
|
A max and case will sort it:
with
data as (
select report_time, m, i ta,
rank() over (order by report_time) rk1,
rank() over (order by report_time desc) rk2
from myTab
)
select report_time, m, i, MAX(CASE WHEN rk1=1 THEN ta END) ta1
MAX(CASE WHEN rk1=2 THEN ta END) ta2
from data
where rk1=1 or rk2=1
GROUP BY report_time, m, i
;
|
|
|
Re: SQL query [message #597128 is a reply to message #597125] |
Tue, 01 October 2013 04:41 |
panyamravi
Messages: 11 Registered: February 2009
|
Junior Member |
|
|
This worked For me: ( along with the solution suggested by Drabjay)
with data
as
(
select report_time , m, i ,ta,
rank() over (order by report_time ) rk1,
rank() over (order by report_time desc ) rk2
from sample_test
)
select m, i , MAX(CASE WHEN rk1=1 THEN ta END) min_tac,
MAX(CASE WHEN rk2=1 THEN tac END) max_ta ,
max (case when rk1=1 then report_time END ) min_rep ,
max (case when rk2=1 then report_time END) max_rep
from data
where rk1=1 or rk2=1
group by m, i
Thanks all.
|
|
|
Re: SQL query [message #597138 is a reply to message #597128] |
Tue, 01 October 2013 05:36 |
|
DrabJay
Messages: 32 Registered: May 2013
|
Member |
|
|
I would say that, as has been pointed out to me on this forum, you should try avoid mixing aggregate and analytic functions if possible. You asked to "collapse" the output to one row, i.e. an aggregation, so it would be better to only use aggregate functions if possible.
|
|
|