Results 1 to 5 of 5
  1. #1
    Join Date
    Apr 2006
    Posts
    12

    Unanswered: top 2 entries for each group FLD1,FLD2,FLD3 (was "SQL SELECT Statement")

    Dear All,
    I have the following set of data, where FLD1, FLD2...FLD4 are the fields.
    I want to be able to select for each group FLD1,FLD2,FLD3 ORDER BY FLD4 ASC the top 2 entries

    Example :
    FLD1 FLD2 FLD3 FLD4
    ABC XXX 1 98
    ABC XXX 1 81
    ABC XXX 1 5
    ABC XXX 2 1
    ABC XXX 2 9
    ABC XXX 2 2
    BBC XXX 2 2

    The result I want is :
    ABC XXX 1 5
    ABC XXX 1 81
    ABC XXX 2 1
    ABC XXX 2 2
    BBC XXX 2 2

    Is there a way to do it using SQL?
    Thanks in advance

  2. #2
    Join Date
    Jun 2003
    Posts
    269
    Code:
    -- create #temp table
    create table #temp 
    (FLD1 varchar(20),
    FLD2  varchar(20),
    FLD3 int,
    FLD4 int
    )
    go
    -- insert query--
    insert into #temp select 'ABC', 'XXX', 1, 98
    insert into #temp select 'ABC', 'XXX' ,1, 81
    insert into #temp select 'ABC' ,'XXX', 1 ,5
    insert into #temp select 'ABC', 'XXX' ,2 ,1
    insert into #temp select 'ABC', 'XXX', 2 ,9
    insert into #temp select 'ABC' ,'XXX', 2 ,2
    insert into #temp select 'BBC' ,'XXX' ,2 ,2
    go
     
    --- finally the select query u want---
    select * from
    (
    select FLD1,FLD2,FLD3,min(FLD4)as FLD4 from #temp
    group by FLD1,FLD2,FLD3
    union all
    select t.FLD1,t.FLD2,t.FLD3,min(t.FLD4) as FLD4 from #temp t
    join 
    (
    select FLD1,FLD2,FLD3,min(FLD4) as FLD4 from #temp 
    group by FLD1,FLD2,FLD3
    ) as t1
    on t.FLD1=t1.FLD1
    and t.FLD2=t1.FLD2
    and t.FLD3=t1.FLD3
    and t.FLD4<>t1.FLD4
    group by t.FLD1,t.FLD2,t.FLD3
    ) as tm
    order by tm.FLD1,tm.FLD2,tm.FLD3,tm.FLD4
    I love cursor,though I never use it.Cos' I dont want to misuse it.
    ----------------------------------------------

    ----cheers
    ----http://mallier.blogspot.com

  3. #3
    Join Date
    Apr 2002
    Location
    Toronto, Canada
    Posts
    20,002
    different query, same result --
    Code:
    select t1.FLD1
         , t1.FLD2
         , t1.FLD3
         , t1.FLD4
      from daTable as t1
    inner
      join daTable as t2
        on t1.FLD1 = t2.FLD1
       and t1.FLD2 = t2.FLD2
       and t1.FLD3 = t2.FLD3
       and t1.FLD4 >= t2.FLD4
    group
        by t1.FLD1
         , t1.FLD2
         , t1.FLD3
         , t1.FLD4
    having count(*) <= 2
    the nice thing about this approach is that it is cake to change it to pull the top 3 or top 5 or top whatever per group
    Last edited by r937; 05-18-06 at 06:32.
    rudy.ca | @rudydotca
    Buy my SitePoint book: Simply SQL

  4. #4
    Join Date
    Jun 2003
    Posts
    269
    the nice thing about this approach is that it is cake to change it to pull the top 3 or top 5 or top whatever per group
    thats cool.not jus cake,cake with icing U deserve
    Last edited by mallier; 05-18-06 at 07:18.
    I love cursor,though I never use it.Cos' I dont want to misuse it.
    ----------------------------------------------

    ----cheers
    ----http://mallier.blogspot.com

  5. #5
    Join Date
    Apr 2006
    Posts
    12
    Thanks r937,
    That is really smart...didn't thought of that..

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •