Post reply

Top 2 records

  • November 16, 2009 at 11:01 am

    #136221

    Hi,

    For following data

    AppName, Date, Query

    TestingApp, 01-01-09 00:00, Select * from tab

    TestingApp, 01-01-09 00:01, Select a from tab

    TestingApp, 01-01-09 00:02, Select b from tab

    TestingApp, 01-01-09 00:03, Select c from tab

    ProductionApp, 01-01-09 00:00, Select x from tab

    ProductionApp, 01-01-09 00:01, Select y from tab

    ProductionApp, 01-01-09 00:02, Select z from tab

    ProductionApp, 01-01-09 00:03, Select * from tab

    I need Top 2 records for each AppName as follows

    AppName, Date, Query

    TestingApp, 01-01-09 00:00, Select * from tab

    TestingApp, 01-01-09 00:01, Select a from tab

    ProductionApp, 01-01-09 00:00, Select x from tab

    ProductionApp, 01-01-09 00:01, Select y from tab

    In my actual table there are alot of AppName and i need Top 2 records for each AppName, so is it possible to get this data without using the cursor for appname or without using the where clause for each appname?

    Thanks

  • November 16, 2009 at 11:07 am

    #1079813

    Use This

    SELECT TOP 2 AppName, Date, Query FROM YOUR_TABLE

    GROUP BY AppName, Date, Query

    Bru Medishetty

    Blog -- LearnSQLWithBru

    Join on Facebook Page Facebook.comLearnSQLWithBru

    Twitter -- BruMedishetty

  • November 16, 2009 at 12:12 pm

    #1079854

    Bru Medishetty (11/16/2009)

    Use This

    SELECT TOP 2 AppName, Date, Query FROM YOUR_TABLE

    GROUP BY AppName, Date, Query

    This would just give 2 records from the entire result set, no? I thought OP wanted 2 from each group,

    will this help?

    drop table #t

    Create table #t(Appname varchar(50), Date datetime, query varchar(200))

    INSERT INTO #t VALUES ('TestingApp', '01-01-09 00:00', 'Select * from tab')

    INSERT INTO #t VALUES ('TestingApp', '01-01-09 00:01', 'Select a from tab')

    INSERT INTO #t VALUES ('TestingApp', '01-01-09 00:02', 'Select b from tab')

    INSERT INTO #t VALUES ('TestingApp', '01-01-09 00:03', 'Select c from tab')

    INSERT INTO #t VALUES ('ProductionApp', '01-01-09 00:01', 'Select Y from tab')

    INSERT INTO #t VALUES ('ProductionApp', '01-01-09 00:03', 'Select * from tab')

    Select * from (

    Select *, row_number() OVER (PARTITION BY Appname ORDER BY Date) as row_num

    from #t) t

    where row_num < = 2

    ---------------------------------------------------------------------------------

  • November 16, 2009 at 12:15 pm

    #1079855

    Bru Medishetty (11/16/2009)

    Use This

    SELECT TOP 2 AppName, Date, Query FROM YOUR_TABLE

    GROUP BY AppName, Date, Query

    I think this is more like what you are seeking

    With toptwo as (SELECT AppName, Date, Query,row_number() over (partition by AppName order by Date) as RowNum

    FROM Your_Table

    GROUP BY AppName, Date, Query

    )

    Select * from toptwo where RowNum <=2

    Jason...AKA CirqueDeSQLeil
    _______________________________________________
    I have given a name to my pain...MCM SQL Server, MVP
    SQL RNNR
    Posting Performance Based Questions - Gail Shaw[/url]
    Learn Extended Events

  • November 16, 2009 at 12:19 pm

    #1079858

    Yes guys,

    I din't have time to create the table, populate data and look at the results.

    Thanks for correcting me.

    Bru Medishetty

    Blog -- LearnSQLWithBru

    Join on Facebook Page Facebook.comLearnSQLWithBru

    Twitter -- BruMedishetty

  • November 16, 2009 at 12:25 pm

    #1079863

    Thanks guys, "ROW_NUMBER/PARTITION BY" worked for me.

Viewing 6 posts - 1 through 5 (of 5 total)

You must be logged in to reply to this topic. Login to reply