selecting top 10 entries from multiple groups

[gregelliott]

Hi,
I'm trying to select the top 10 entries from a table per a variable. At present the below query brings up only 10 entries but I want 10 entries per segmentcode.



SELECT top 10 * FROM t_security WHERE segmentcode IN(

'AIM','AIM3','AIMI','AMSM','ASQ1','ASQ2','ASX1','A SX2','ASXN','CIBB','CNVE','CRNR','CRTR','CSEQ','CS ET','CSQX',
'CWNR','CWNU','CWTR','CWTU','EQS','ETCS','ETF2','E TFS','GILT','INSD','IOB','IOBU','IRSQ','IRSU','ITB B','ITBU',
'ITR','LVSD','MIBB','MISC','MISL','MSEQ','MSET','M SQX','MSTM','NSTS','ODTT','PSNR','PSTR','RSET','RS TM','SET1',
'SET2','SET3','SSMM','SSMU','SSQ3','SSX3','SSX4',' STBS','STMM','TC1E','TC1M','TC1O','TCAE','TCAM','T CAO','TEST',
'XIBB','XSEQ','XSET','XSTM','ZA01','ZA02','ZA03',' ZA04','ZA11','ZA21','ZA22','ZA24'
)

[almaz]

Hi,
I'm trying to select the top 10 entries from a table per a variable. At present the below query brings up only 10 entries but I want 10 entries per segmentcode.

SQL Server 2005 solution (don't forget to replace YOUR_FIELDS_HERE with correct field list, ORDER_FIELD with correct field name):

;with Ordered(YOUR_FIELDS_HERE, Position) as
(
    select YOUR_FIELDS_HERE, Position = row_number() over(partition by segmentcode order by ORDER_FIELD)
    from t_security
    where segmentcode in(
        'AIM','AIM3','AIMI','AMSM','ASQ1','ASQ2','ASX1','A  SX2','ASXN','CIBB','CNVE','CRNR','CRTR','CSEQ','CS  ET','CSQX',
        'CWNR','CWNU','CWTR','CWTU','EQS','ETCS','ETF2','E  TFS','GILT','INSD','IOB','IOBU','IRSQ','IRSU','ITB  B','ITBU',
        'ITR','LVSD','MIBB','MISC','MISL','MSEQ','MSET','M  SQX','MSTM','NSTS','ODTT','PSNR','PSTR','RSET','RS  TM','SET1',
        'SET2','SET3','SSMM','SSMU','SSQ3','SSX3','SSX4','  STBS','STMM','TC1E','TC1M','TC1O','TCAE','TCAM','T  CAO','TEST',
        'XIBB','XSEQ','XSET','XSTM','ZA01','ZA02','ZA03','  ZA04','ZA11','ZA21','ZA22','ZA24'
    )
)
select YOUR_FIELDS_HERE 
from Ordered
where Position<=10