CODEWITHSUNDEEP
sqloracle-databasegreatest-n-per-group
user747291
user747291

Reputation: 821

Oracle get first record from result set

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

Upvotes: 0

Views: 326

Answers (2)

sstan
sstan

Reputation: 36543

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

Upvotes: 1

xQbert
xQbert

Reputation: 35343

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

Upvotes: 1

Related Questions