SELECT DISTINCT smp.treatmentid,
                smpprd.sampleproductcode,
                (SELECT spcnum
                 FROM   (SELECT DISTINCT smp2.treatmentid,
                                         smpprd2.sampleproductcode,
                                         ROWNUM AS spcnum
                         FROM   gpl.gpl_samples smp2,
                                gpl.gpl_sampleproductlists smpprdlst2,
                                gpl.gpl_sampleproducts smpprd2
                         WHERE  smpprdlst2.sampleid = smp2.sampleid
                                AND smpprdlst2.sampleproductid =
                                    smpprd2.sampleproductid
                                AND smp2.treatmentid = 301
                         ORDER  BY smpprd2.sampleproductcode)
                 WHERE  sampleproductcode = smpprd.sampleproductcode) AS spcnum
FROM   gpl.gpl_samples smp,
       gpl.gpl_sampleproductlists smpprdlst,
       gpl.gpl_sampleproducts smpprd
WHERE  smpprdlst.sampleid = smp.sampleid
       AND smpprdlst.sampleproductid = smpprd.sampleproductid
       AND smp.trialid = 3163;