Post reply

script help

  • May 3, 2010 at 11:40 pm

    #103934

    Hi

    Can you please help me write a query

    Sample data

    Col1 col2

    1 1

    1 2

    1 3

    2 4

    2 5

    2 6

    ...

    ...

    Here col1 can have duplicate data but col2 will always have unique value.

    I need a query like for each col1 , show me the 10 results from col2.

  • May 4, 2010 at 2:15 am

    #1161647

    Did you notice ? this post has 9 views and still NO reply , just becoz of incomplete information.

    Atleast post desired result for sample data.

    -------Bhuvnesh----------
    I work only to learn Sql Server...though my company pays me for getting their stuff done;-)

  • May 4, 2010 at 2:27 am

    #1161652

    sample data :

    col1 col2

    1 1

    1 2

    1 3

    1 4

    2 5

    2 6

    1 7

    3 8

    2 9

    I would like to get only 10 rows for each unique col1.

    (for simplicity here only few rows are taken, while my table has billions of rows)

    My result should look like

    col1 col2

    1 1

    1 2

    1 3

    1 4

    1 7

    2 5

    2 6

    2 9

    In brief: Show only 10 rows from col2 for each unique col1

  • May 4, 2010 at 3:35 am

    #1161671

    khushbu (5/4/2010)

    sample data :

    col1 col2

    1 1

    1 2

    1 3

    1 4

    2 5

    2 6

    1 7

    3 8

    2 9

    I would like to get only 10 rows for each unique col1.

    (for simplicity here only few rows are taken, while my table has billions of rows)

    My result should look like

    col1 col2

    1 1

    1 2

    1 3

    1 4

    1 7

    2 5

    2 6

    2 9

    In brief: Show only 10 rows from col2 for each unique col1

    try this

    declare @t table ( col1 int, col2 int)

    insert into @t

    select 1 , 1 union all

    select 1 , 2 union all

    select 1 , 3 union all

    select 1 , 4 union all

    select 2 , 1 union all

    select 2 , 72 union all

    select 2 , 63 union all

    select 2 , 4 union all

    select 2 , 45 union all

    select 2 , 46 union all

    select 2 , 7 union all

    select 2 , 48 union all

    select 2 , 39 union all

    select 2 , 10 union all

    select 2 , 71 union all

    select 2 , 82

    select col1 , col2 from

    (

    select col1 , col2 , row_number() over ( partition by col1 order by col1 ) count

    from @t )t where count < 11

    -------Bhuvnesh----------
    I work only to learn Sql Server...though my company pays me for getting their stuff done;-)

  • May 4, 2010 at 3:36 am

    #1161672

    khushbu (5/4/2010)

    (for simplicity here only few rows are taken, while my table has billions of rows)

    give a close look on indexes used for your query

    -------Bhuvnesh----------
    I work only to learn Sql Server...though my company pays me for getting their stuff done;-)

  • May 6, 2010 at 10:39 pm

    #1163778

    Why not to use Top(N) clause with Order by Col1?

    I think it should solve the problem.

    ----------------------------------------------------------------------------------------------------------------------------------------------------------------------
    Sometimes, winning is not an issue but trying.
    You can check my BLOG     [font="Arial Black"]here[/font][/url][/right]

  • May 7, 2010 at 1:41 am

    #1163846

    Atif Sheikh (5/6/2010)

    Why not to use Top(N) clause with Order by Col1?

    again Indexes will matter here

    -------Bhuvnesh----------
    I work only to learn Sql Server...though my company pays me for getting their stuff done;-)

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

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