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','A IMI','AMSM','AS Q1','ASQ2','ASX 1','A SX2','ASXN','CI BB','CNVE','CRN R','CRTR','CSEQ ','CS ET','CSQX',
'CWNR','CWNU',' CWTR','CWTU','E QS','ETCS','ETF 2','E TFS','GILT','IN SD','IOB','IOBU ','IRSQ','IRSU' ,'ITB B','ITBU',
'ITR','LVSD','M IBB','MISC','MI SL','MSEQ','MSE T','M SQX','MSTM','NS TS','ODTT','PSN R','PSTR','RSET ','RS TM','SET1',
'SET2','SET3',' SSMM','SSMU','S SQ3','SSX3','SS X4',' STBS','STMM','T C1E','TC1M','TC 1O','TCAE','TCA M','T CAO','TEST',
'XIBB','XSEQ',' XSET','XSTM','Z A01','ZA02','ZA 03',' ZA04','ZA11','Z A21','ZA22','ZA 24'
)

[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_HER E 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