Oracle get first record from result set

Question

I have a query like, below which gives the following results. For one given pcid there may be one or more images. I only want to grab the first record if the pcid has more than 1 images returned. I know distinct will not work in this case. Any ideas will be appreciated. thanks

select p.pcid,p.pc_name,p.pc_image_name
from pc p LEFT JOIN pc_goods pcm on p.pcid = pcm.pcid AND pcm.media_type = 'I' 
AND pcm.act_ind = 'Y' AND pcm.fea_ind = 'Y' INNER JOIN pc_cat pcat 
ON p.category_id = pcat.cat_id 
and p.fea_ind = 'Y' AND p.act_ind = 'Y'
order by pcid

Current results     
    pcid    pc_name   pc_image_name
    --------------------------------
    1120    testone   one.jpg
    1120    testwo    two.jpg
    1121    testthree three.png
    1125    testfour  four.png
    1128    test5     five.jpg
    1128    test6     six.jpg

Desired results,

pcid    pc_name   pc_image_name
--------------------------------
1120    testone   one.jpg
1121    testthree three.png
1125    testfour  four.png
1128    test5     five.jpg

How do you denote "First" is there a date time or do you just need any record? If so you could always use min on image name in a subquery then join back.. Use an inline view of the PC_goods table to return a single record for each PCID using min/max then join it into the result set . Or you could generate a row number field and return where it's only 1 for each PCiD. [Example:](http://stackoverflow.com/questions/10477085/oracle-partition-by-and-row-number-keyword) — xQbert, Oct 05 '15 at 14:38
I don't see why your query needs to do a left join on `pc_goods`? — sstan, Oct 05 '15 at 14:52

score 1 · Answer 1 · answered Oct 05 '15 at 14:56

Without testing or addressing why a left join is needed... something like this would probably work provided an imageName is unique.

SELECT p.pcid,p.pc_name,p.pc_image_name
FROM pc p 
LEFT JOIN pc_goods pcm 
  on p.pcid = pcm.pcid AND pcm.media_type = 'I' 
 AND pcm.act_ind = 'Y' AND pcm.fea_ind = 'Y' 
INNER JOIN (SELECT max(pc_image_name) maxImage, pcID from PC_GOODS group by PCID) PCM2
 on PCM2.maxImage=PCM.Pc_image_name 
and PCM2.PCID = PCM2.PCID
INNER JOIN pc_cat pcat 
  ON p.category_id = pcat.cat_id 
  and p.fea_ind = 'Y' AND p.act_ind = 'Y'
order by pcid

score 1 · Accepted Answer · answered Oct 05 '15 at 14:56

1

You can use the row_number window function for this:

row_number() over (partition by p.pcid order by null) as rn

...where you partition by pcid. Since you don't seem to care which image is picked, you can just order by any constant value (I used a null in this case).

select pcid,pc_name,pc_image_name
  from (
     select p.pcid,p.pc_name,p.pc_image_name,
            row_number() over (partition by p.pcid order by null) as rn
    from pc p LEFT JOIN pc_goods pcm on p.pcid = pcm.pcid AND pcm.media_type = 'I' 
    AND pcm.act_ind = 'Y' AND pcm.fea_ind = 'Y' INNER JOIN pc_cat pcat 
    ON p.category_id = pcat.cat_id 
    and p.fea_ind = 'Y' AND p.act_ind = 'Y')
 where rn = 1
 order by pcid

answered Oct 05 '15 at 14:56

sstan

35,425
6
48
66

Thanks! this works perfectly, Is there a simple example to explain the partiionby and row_number() functions? I am not very familiar with these and I know these come very handy in oracle. I did try to look up online but most of them are too complicated. – user747291 Oct 05 '15 at 15:13
1

[General analytic function information](http://docs.oracle.com/cd/E11882_01/server.112/e41084/functions004.htm#SQLRF06174) and [row_number specific info](http://docs.oracle.com/cd/E11882_01/server.112/e41084/functions156.htm#SQLRF06100) – sstan Oct 05 '15 at 15:17

Oracle get first record from result set

2 Answers2