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.
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.