Home » SQL & PL/SQL » SQL & PL/SQL » SQL query
SQL query [message #597110] Tue, 01 October 2013 02:57 Go to next message
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 #597111 is a reply to message #597110] Tue, 01 October 2013 03:24 Go to previous messageGo to next message
DrabJay
Messages: 32
Registered: May 2013
Member
Given the data you supplied you could try:
SELECT m,
       i,
       min(report_time),
       max(report_time),
       min(ta) KEEP (DENSE_RANK FIRST ORDER BY report_time),
       max(ta) KEEP (DENSE_RANK LAST ORDER BY report_time)
FROM your_table
GROUP BY m,
         I

Re: SQL query [message #597116 is a reply to message #597111] Tue, 01 October 2013 03:59 Go to previous messageGo to next message
panyamravi
Messages: 11
Registered: February 2009
Junior Member
Thanks a lot DrabJay.

I tried with Rank() but could not crack it. Thanks again.
icon3.gif  Re: SQL query [message #597117 is a reply to message #597110] Tue, 01 October 2013 03:59 Go to previous messageGo to next message
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

icon2.gif  Re: SQL query [message #597118 is a reply to message #597116] Tue, 01 October 2013 04:00 Go to previous messageGo to next message
Michel Cadot
Messages: 68647
Registered: March 2007
Location: Nanterre, France, http://...
Senior Member
Account Moderator

See my previous post with rank (posted at the same than your post).

Re: SQL query [message #597122 is a reply to message #597118] Tue, 01 October 2013 04:23 Go to previous messageGo to next message
panyamravi
Messages: 11
Registered: February 2009
Junior Member
Thanks Michel..!!

I almost tried the same, but the output will come in two lines.

I want it in one row with Min and Max on the same row!.

[Updated on: Tue, 01 October 2013 04:27]

Report message to a moderator

Re: SQL query [message #597125 is a reply to message #597122] Tue, 01 October 2013 04:30 Go to previous messageGo to next message
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 Go to previous messageGo to next message
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 Go to previous message
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.
Previous Topic: CASE statement
Next Topic: .pkb to .pls
Goto Forum:
  


Current Time: Fri Apr 26 22:54:14 CDT 2024