Oracle FAQ Your Portal to the Oracle Knowledge Grid
HOME | ASK QUESTION | ADD INFO | SEARCH | E-MAIL US
 

Home -> Community -> Usenet -> c.d.o.server -> Re: Query Help

Re: Query Help

From: Martin Haltmayer <Martin.Haltmayer_at_0800-einwahl.de>
Date: Fri, 15 Jun 2001 01:04:20 +0200
Message-ID: <3B2942F4.4197B761@0800-einwahl.de>

In 8.1.6 and above you do

select	col1
	, col2
	, to_char(dDATE, 'mm/dd/yyyy hh:mi:ss') as max_ddate
	, result
from (
select	COL1
	, COL2
	, dDATE
	, result
	, dense_rank() over (partition by col1, col2 order by ddate desc) as rk
from tableone
)
where rk = 1
/

which yields

COL1 COL2 MAX_DDATE RESULT

---------- ---------- ------------------- ----------
A          B          06/11/2001 06:01:22       3.55
AA         BB         06/11/2001 10:11:00         .6

Martin

Arun Solleti wrote:
>
> Hi
> I wanted some help in writing a query whose description is as below
>
> ---------------------------------------------------------------------------
>
> Table Name - tableone
> Its Columns
> COL1 COL2 RESULT USER DATE
> ----------------------------------------------------------------------
> A B PASS abc
> 06/11/2001 07:15:12
> A B FAIL abc
> 06/11/2001 11:05:04
> A B BLOCK abc 06/11/2001
> 18:01:22
> AA BB FAIL bcd
> 06/11/2001 02:11:22
> AA BB FAIL bcd
> 06/11/2001 06:06:03
> AA BB PASS abc 06/11/2001
> 10:11:00
> -----------------------------------------------------------------------
>
> I want to run a query grouping by COL1, & COL2 and get the MAX(DATE),
> and i also want to retrieve corresponding RESULT value for that row. So
> i have return the followin :
>
> QUERY
> ---------
> select COL1, COL2, to_char(max(DATE), 'mm/dd/yyyy hh:mi:ss') from
> tableone group by COL1, COL2 ;
>
> which gives me what i want as follows but it is missin the RESULT Column
>
> --------------------------------------------------------------------------
>
> COL1 COL2 DATE
> ----------------------------------------------------------------------
> A B 06/11/2001 18:01:22
> AA BB 06/11/2001 10:11:00
> -----------------------------------------------------------------------
>
> How can i get RESULT COLUMN into the above result because if i add
> RESULT to my select statment then it is not in the GROUP BY and sql give
> an error and i do not want to add RESULT in group by because i want just
> COL1 and COL2 to group by with.
>
> Thanks for Suggestions
> Arun
Received on Thu Jun 14 2001 - 18:04:20 CDT

Original text of this message

HOME | ASK QUESTION | ADD INFO | SEARCH | E-MAIL US