Re: SQL Query question

From: Thomas Kyte <tkyte_at_us.oracle.com>
Date: Wed, 03 Jun 1998 22:52:51 GMT
Message-ID: <3578d364.27848353@192.86.155.100>

A copy of this was sent to bobmc_at_cyberramp.net (Bob McConnell) (if that email address didn't require changing) On Wed, 03 Jun 1998 21:43:31 GMT, you wrote:

select a.id, a.field3, a.field4, ...., b.max_taskno from T a, ( select max(taskno) max_taskno, field3

              from T 
             group by field3 ) b

where a.field3 = b.field3;

should do it....

>Actually I'm trying to retrieve the complete records which contain
>several other fields. So what I'm trying to accomplish is
>select MAX(taskno),id, otherfield1,otherfield2,etc.
>
>How do I get by "NOT A SINGLE GROUP GROUP FUNCTION"
>error for the other fields I need?
>
>BYOn Tue, 02 Jun 1998 07:45:17 -0500, kiel <kiel_at_webpre.com> wrote:
>
>>Bob McConnell wrote:
>>>
>>> Need help with:
>>>
>>> I have a table with columns ID and TASKNO which
>>> looks like:
>>>
>>> TASKNO ID field3 field4 field5
>>> 1 JOB1
>>> 2 JOB1
>>> 3 JOB1
>>> 1 JOB2
>>> 2 JOB2
>>> 1 JOB3
>>> 2 JOB3
>>>
>>> I need to be able to select one of each ID
>>> based on the highest TASKNO
>>>
>>> Resulting in
>>> TASKNO ID field3 field4 field5
>>> 3 JOB1 data data data
>>> 2 JOB2 data data data
>>> 2 JOB3 data data data
>>>
>>> Thanks,
>>> Bob
>>
>>do you mean
>>
>>select max(taskno), id
>> from table
>> group by id
>>
>>?
>>
>>Christian
 

Thomas Kyte
tkyte_at_us.oracle.com
Oracle Government
Herndon VA  

http://govt.us.oracle.com/ -- downloadable utilities  

---

Opinions are mine and do not necessarily reflect those of Oracle Corporation  

Anti-Anti Spam Msg: if you want an answer emailed to you, you have to make it easy to get email to you. Any bounced email will be treated the same way i treat SPAM-- I delete it. Received on Wed Jun 03 1998 - 17:52:51 CDT