sql group by problem

[joehnk]

I am having probems with getting the results that I need.
I am running oracle 7. I believe that more recent versions might have made this pretty easy, but...

I am providing a basic example of the data and what I hope to achieve.

Any assistance is greatly appreciated.

TIME MODEL SERIALNUM CODE
8:00 SRY 0123 0502
8:01 SRY 0124 0502
8:03 SRD 0125 0504
8:04 SRD 0126 0502
8:05 SRD 0126 0504


what I am trying to do is group by codes - but if I have a serial num listed more than once - only include the most recent time for that entry

currently I have:
select code, count(code) from final_collection
where code <> 'GOOD'
group by code

returns:

CODE count(code)
0502 3
0504 2


what I want is:

CODE count(code)
0502 2
0504 2

where the code 0502 for serialnum 0126 is not included since it's time is earlier than the 0504 record.


Thanks so much for any help.

 

[SantaMufasa]

Joe,

Here is a solution for you:

Section 1 -- Sample data:

select * from final_collection;

TIME MOD  SERIALNUM CODE
---- --- ---------- ----
8:00 SRY        123 0502
8:01 SRY        124 0502
8:03 SRD        125 0504
8:03 SRD        125 GOOD
8:04 SRD        126 0502
8:05 SRD        126 0504

6 rows selected.

Section 2 -- Solution code:

select code, count(code)
  from (select * from final_collection a
         where time = (select max(time)
                         from final_collection
                        where a.serialnum = serialnum
                          and code <> 'GOOD'))
 where code <> 'GOOD'
 group by code
;

CODE COUNT(CODE)
---- -----------
0502           2
0504           2

Let us know if this is what you wanted.


Mufasa
(aka Dave of Sandy, Utah, USA)
[

http://www.dasages.com:

Providing low-cost remote Database Admin services]
Click here to join Utah Oracle Users Group on Tek-Tips if you use Oracle in Utah USA.