Getting a single value from grouped rows 1

[Varith]

I'm trying to figure out an efficient method of grabbing the first value in a column where the rows have been grouped but I do not want them grouped on that particular column. I have a table that looks like this:

TRANSNUM TRANS_CODE
---------------------------
1 RN
1 RN
1 RN
2 N
2 CN
3 N
4 E
4 E
4 E
5 N
5 E

This table is then aggregated into something like:

TRANSNUM COUNT TRANS_CODE
-----------------------------------
1 3 RN
2 2 N
3 1 N
4 3 E
5 2 N

The logic is to count the per TRANSNUM and set the value of TRANS_CODE as the value of the first row in the group. I can't simply group on TRANS_CODE because it may not have the same value for every group of TRANSNUM

Right now I use a query:

SELECT TRANSNUM, COUNT (TRANSNUM), (SELECT TRANS_CODE FROM TRANS WHERE TRANSNUM=T.TRANSNUM AND ROWNUM = 1) FROM TRANS T.

This works, but it is pretty slow. Is there any more efficient way to do it? Thanks.

 

[SantaMufasa]

Varith,

Except for your missing code line, "Group by transnum;" I see nothing wrong with your code. I ran it and it seemed to perform quickly enough. Of course I haven't the volumes of data that you probably have, but I see nothing inefficient about your code:

SELECT TRANSNUM
    , COUNT (TRANSNUM) count
    , (SELECT TRANS_CODE
       FROM TRANS
       WHERE TRANSNUM=T.TRANSNUM
         AND ROWNUM = 1) trans_code
FROM TRANS T
 Group by transnum;

  TRANSNUM      COUNT TR
---------- ---------- --
         1          3 RN
         2          2 N
         3          1 N
         4          3 E
         5          2 N

Mufasa
(aka Dave of Sandy, Utah, USA)

http://www.dasages.com

Do you use Oracle and live or work in Utah, USA?
Then click here to join Utah Oracle Users Group on Tek-Tips.

 

[sem]

The table just looks like so. In fact if you query the same table (unchanged!) later there's a non-zero probability that the order of rows would be other. You should provide better criteria to choose a value you need from a set. How is N in TRANSNUM=2 better than CN?

Regards, Dima

http://seminihin.narod.ru

 

[taupirho]

See if this is any faster for you:

select distinct transmum,
count(transnum) over(partition by transnum) count,
first_value(trans_code) over(partition by transnum) tr
from trans

 

[Varith]

Mufasa,
You are right, it works, It is just that the execution time for the query (which is a little more complicated than I showed) jumps from 3-4 seconds to 25-30 when I add in that extra select statement.

Sem,
Yeah, I pointed out the ordering problem also. Specifically the values 'N' and 'RN' indicate a new policy and (so I have been assured) are never mixed in with other values in a single transnum. The other values can be mixed. Seem like a shaky design to me but it is what is there.

Tom,
I think the analytic function, which you just showed me, are probably just what I need here. I'll school myself on those further. Thanks.