Re: SQL Query Help

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

Query the results of the initial query as in:

SELECT field1, field2, field3, calculation(*) FROM (
   SELECT field1, field2, field3
   FROM sometables)
GROUP BY 1,2,3; Daniel A. Morgan Received on Wed Jun 13 2001 - 00:06:49 CDT