SQL Server Performance

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

Discussion in 'Getting Started' started by Janani Priya, Mar 26, 2008.

  1. Janani Priya New Member

    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
  2. ranjitjain New Member

    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
  3. Adriaan New Member

    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)
  4. Janani Priya New Member

    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





  5. ranjitjain New Member

    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
  6. Janani Priya New Member

    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...
  7. sam2mis New Member

    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()
  8. Janani Priya New Member

    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
  9. Madhivanan Moderator

    [quote user="sam2mis"]
    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()
    [/quote]
    No. You need to read about Row_number() in sql server 2005 help file

Share This Page