How to select top 1 record in each set of id respectively?

hai guys...
am new to sql server 2005....am using in stored procedure......plz help me out........
the below is my table structure.....
Table Name : #temp
pgm_main_category_id pgm_sub_category_id filepath

17 15 photo/Writer/Content/Sports/Basketball/bb1.jpg
17 16 photo/Writer/Content/Sports/Cricket/cricket1.jpg
17 17 photo/Writer/Content/Sports/BaseBall/base1.jpg
18 18 photo/Writer/Content/Nature/Forest/forest1.jpg
18 19 photo/Writer/Content/Nature/Tree/tree1.jpg
18 20 photo/Writer/Content/Nature/Flower/flower1.jpg
19 21 photo/Writer/Content/Gadget/Laptop/laptop1.jpg
19 22 photo/Writer/Content/Gadget/DigitalCamera/camer1.jpg
19 23 photo/Writer/Content/Gadget/Mobile/cybermbl1.jpg
17 24 photo/Writer/Content/Sports/Formula1/F1.jpg
from this table i need the query output as below......
for example: This is my expected output
pgm_main_category_id pgm_sub_category_id filepath

17 15 photo/Writer/Content/Sports/Basketball/bb1.jpg
18 18 photo/Writer/Content/Nature/Forest/forest1.jpg
19 21 photo/Writer/Content/Gadget/Laptop/laptop1.jpg
17 16 photo/Writer/Content/Sports/Cricket/cricket1.jpg
18 19 photo/Writer/Content/Nature/Tree/tree1.jpg
19 22 photo/Writer/Content/Gadget/DigitalCamera/camer1.jpg
17 17 photo/Writer/Content/Sports/BaseBall/base1.jpg
18 20 photo/Writer/Content/Nature/Flower/flower1.jpg
19 23 photo/Writer/Content/Gadget/Mobile/cybermbl1.jpg
17 24 photo/Writer/Content/Sports/Formula1/F1.jpg
if anybody have an idea about this query plz send me...... i need this immediately guys.....
thanks in advance......
regards
janu

Your desired result is showing all the rows stored in the temp table. Are you looking for ordering such sets? IF you are looking for only top one set then consider below query else elaborate more on the result.declare
@t1 table(pgm_main_category_id int, pgm_sub_category_id int, filepath varchar(2000))insert
@t1 select 17,15,'photo/Writer/Content/Sports/Basketball/bb1.jpg' UNION ALL
select
17,16,'photo/Writer/Content/Sports/Cricket/cricket1.jpg' UNION ALLselect 17,17,'photo/Writer/Content/Sports/BaseBall/base1.jpg' UNION ALL
select
18,18,'photo/Writer/Content/Nature/Forest/forest1.jpg' UNION ALLselect 18,19,'photo/Writer/Content/Nature/Tree/tree1.jpg' UNION ALL
select
18,20,'photo/Writer/Content/Nature/Flower/flower1.jpg' UNION ALLselect 19,21,'photo/Writer/Content/Gadget/Laptop/laptop1.jpg' UNION ALL
select
19,22,'photo/Writer/Content/Gadget/DigitalCamera/camer1.jpg' UNION ALLselect 19,23,'photo/Writer/Content/Gadget/Mobile/cybermbl1.jpg' UNION ALL
select
17,24,'photo/Writer/Content/Sports/Formula1/F1.jpg'select
t1.* FROM @t1 t1JOIN
(select pgm_main_category_id,min(pgm_sub_category_id) pgm_sub_category_id from @t1group
by pgm_main_category_id) tmp ONt1
.pgm_main_category_id=tmp.pgm_main_category_id AND t1.pgm_sub_category_id=tmp.pgm_sub_category_id

Alternatively:
SELECT t1.pgm_main_category_id, t1.pgm_sub_category_id, t1.filepath
FROM #tmp t1
WHERE t1.pgm_sub_category_id =
(SELECT MIN(t2.pgm_sub_category_id)
FROM #tmp t2 WHERE t2.pgm_main_category_id = t1.pgm_main_category_id)

hi, thank u so much for ur reply mr.ranjitjain...
i work out ur query.... it sounds great.... i get the result output as
pgm_main_category_id pgm_sub_category_id filepath
17 15 photo/Writer/Content/Sports/Basketball/bb1.jpg
18 18 photo/Writer/Content/Nature/Forest/forest1.jpg
19 21 photo/Writer/Content/Gadget/Laptop/laptop1.jpg
but i need alternate output as below in a single query.......
pgm_main_category_id pgm_sub_category_id filepath
17 15 photo/Writer/Content/Sports/Basketball/bb1.jpg
18 18 photo/Writer/Content/Nature/Forest/forest1.jpg
19 21 photo/Writer/Content/Gadget/Laptop/laptop1.jpg
17 16 photo/Writer/Content/Sports/Cricket/cricket1.jpg
18 19 photo/Writer/Content/Nature/Tree/tree1.jpg

19 22 photo/Writer/Content/Gadget/DigitalCamera/camer1.jpg
Is it possible or not......
thanks & regards
janu

Hi,
I guess you are looking for ordering instead of top 1 result, try below query by replacing the table variable with actual table name:select
pgm_main_category_id,pgm_sub_category_id,filepath,order_seq FROM(select
*,order_seq=ROW_NUMBER() OVER(PARTITION BY pgm_main_category_id ORDER BY pgm_main_category_id,pgm_sub_category_id) from @t1)
tmp order by order_seq,pgm_main_category_id

hi, sorry just now i noticed the version of sql server.... it is sqlserver 2000.......
here am not able to use the inbuilt function ROW_NUMBER()....
is there any other way to do that process......in sql 2000...

Try using Record Count instead of ROW_NUMBER()
recordcount() or obj.recordcount()
I hope these are the tags used in SQL Server 2000 instead of ROW_NUMBER()

hi......[]
i got the exact result for my expected output by using the below query:
this should be used for sql 2000
SELECT pgm_main_category_id, pgm_sub_category_id, filepath FROM (SELECT pgm_main_category_id, pgm_sub_category_id, filepath,
(SELECT count(*) FROM groupingData$ b WHERE b.pgm_sub_category_id<a.pgm_sub_category_id
AND a.pgm_main_category_id=b.pgm_main_category_id) as rnumFROM groupingData$ a) t
ORDER BY rnum, pgm_main_category_id
The same query for sql 2005:
--SQL Server 2005SELECT t.pgm_main_category_id, t.pgm_sub_category_id, t.filepath FROM (SELECT pgm_main_category_id, pgm_sub_category_id, filepath,
ROW_NUMBER() OVER(PARTITION BY pgm_main_category_id ORDER BY pgm_sub_category_id) as rnum
FROM groupingData$) t
ORDER BY t.rnum, t.pgm_main_category_id
these are the exact solutions.....make use of it if u need
thanks for all your responses.........
regards
janu