Oracle FAQ | Your Portal to the Oracle Knowledge Grid |
Home -> Community -> Usenet -> c.d.o.server -> Re: Query Help
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 rkfrom tableone
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