Re: Query Help

select b.col1, b.col2, b.maxdate, a.result from tableone a, (select COL1, COL2, to_char(max(DATE), 'mm/dd/yyyy hh:mi:ss') as maxdate from

                           tableone group by COL1, COL2 ) b
where a.col1 =b.col1 and
          a.col2 = b.col2 and
          a.date = b.maxdate;

I think this should work.

Kenny

"Arun Solleti" <asolleti_at_grad.csee.usf.edu> wrote in message news:3B26D9A2.121299BC_at_grad.csee.usf.edu...
> 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 Sat Jul 21 2001 - 23:51:48 CEST