interesting query

  • I wonder if anyone can help with this query which I cant figure out?

    I want to limit the amount of rows (or groups) returned, it could be any number but in this case lets say 8 rows, the problem is that it must include all items within 'grp'. The results are to be ordered in the same order as entered into the table. ie dte, 'grp'.

    So, my example of returning 8 rows returns everything else correctly BUT would return only half of the '445' group. How can I construct a query that has some kind of look ahead and continues to return rows until all items with the 'grp' are returned (in this case it should actually return 13 rows?

    I'm pretty sure CTE is the answer, but cant work out how.

    Many thanks in advance, your assistance would be much appreciated.

    declare @tbl table (id int identity(1,1), grp int, dte datetime) -- live data contains many additional columns

    INSERT @tbl values (1, '20 jan 2011')

    INSERT @tbl values (1, '21 jan 2011')

    INSERT @tbl values (2, '22 jan 2011')

    INSERT @tbl values (2, '22 jan 2011')

    INSERT @tbl values (445, '26 jan 2011')

    INSERT @tbl values (445, '26 jan 2011')

    INSERT @tbl values (445, '26 jan 2011')

    INSERT @tbl values (445, '27 jan 2011')

    INSERT @tbl values (445, '27 jan 2011')

    INSERT @tbl values (445, '28 jan 2011')

    INSERT @tbl values (445, '28 jan 2011')

    INSERT @tbl values (445, '28 jan 2011')

    INSERT @tbl values (445, '29 jan 2011')

    INSERT @tbl values (6, '1 feb 2011')

    INSERT @tbl values (8, '3 feb 2011')

    INSERT @tbl values (1, '30 mar 2011')

    INSERT @tbl values (1, '30 mar 2011')

    INSERT @tbl values (1, '30 mar 2011')

    ;

    WITH CTE AS

    (

    SELECT *,ROW_NUMBER() OVER

    (PARTITION BY grp

    ORDER BY dte ASC, grp)

    AS RowNumber

    FROM @tbl tbl

    )

    SELECT * FROM CTE

    order by dte, grp

    112011-01-20 00:00:00.0001

    212011-01-21 00:00:00.0002

    322011-01-22 00:00:00.0001

    422011-01-22 00:00:00.0002

    54452011-01-26 00:00:00.0001

    64452011-01-26 00:00:00.0002

    74452011-01-26 00:00:00.0003

    84452011-01-27 00:00:00.0004

    94452011-01-27 00:00:00.0005

    104452011-01-28 00:00:00.0006

    114452011-01-28 00:00:00.0007

    124452011-01-28 00:00:00.0008

    134452011-01-29 00:00:00.0009

    1462011-02-01 00:00:00.0001

    1582011-02-03 00:00:00.0001

    1612011-03-30 00:00:00.0003

    1712011-03-30 00:00:00.0004

    1812011-03-30 00:00:00.0005

  • What about grp 1?

    Do you want to return entries for grp 1 in records 16, 17 and 18 as well as 1 and 2?

    If not - can you specify formal criteria why rows 16, 17, 18 should not be included?

    _____________
    Code for TallyGenerator

  • Thankyou for you reply.

    grps can be updated many times on many dates, so can appear in the table many times.

    The table is ordered by date, so I am only interested in the first 'n' rows plus however many rows needed to get all items for the grp.

    In this case, its 8 + 5 rows.

    So the answer to your question is I am only interested in rows 1-13 and not any subsequent rows

  • AllanP999 (3/13/2013)


    Thankyou for you reply.

    grps can be updated many times on many dates, so can appear in the table many times.

    The table is ordered by date, so I am only interested in the first 'n' rows plus however many rows needed to get all items for the grp.

    In this case, its 8 + 5 rows.

    So the answer to your question is I am only interested in rows 1-13 and not any subsequent rows

    Here's one solution:

    with Groups

    as (

    select top 8 grp

    from @tbl

    order by dte

    )

    ,GroupsDistinct

    as (

    select distinct grp

    from Groups

    )

    select t.*

    from @tbl t

    join GroupsDistinct gd on t.grp = gd.grp

    This does, however, bring back ids 16, 17 and 18 because they are in grp 1 (which is selected by the Id 1 row). If these rows should not be selected, you need to modify your description of the problem, as already pointed out by Sergiy.

    If you haven't even tried to resolve your issue, please don't expect the hard-working volunteers here to waste their time providing links to answers which you could easily have found yourself.

  • Here's another, same caveats apply

    WITH CTE AS

    (

    SELECT id,grp,dte,

    ROW_NUMBER() OVER(ORDER BY dte ASC, grp) AS rn

    FROM @tbl tbl

    ),

    CTE2 AS (

    SELECT id,grp,dte,rn,

    MIN(rn) OVER(PARTITION BY grp) AS MinRn

    FROM CTE)

    SELECT id,grp,dte

    --,rn,MinRn

    FROM CTE2

    WHERE MinRn <= 8

    ORDER BY rn;

    ____________________________________________________

    Deja View - The strange feeling that somewhere, sometime you've optimised this query before

    How to get the best help on a forum

    http://www.sqlservercentral.com/articles/Best+Practices/61537
  • Great I now have the solution. Many thanks to you all.

  • AllanP999 (3/14/2013)


    Great I now have the solution. Many thanks to you all.

    Which one did you use or was it a combination? It would be great if you would share what you used as a solution to help other that may stumble into this thread in the future.

    _______________________________________________________________

    Need help? Help us help you.

    Read the article at http://www.sqlservercentral.com/articles/Best+Practices/61537/ for best practices on asking questions.

    Need to split a string? Try Jeff Modens splitter http://www.sqlservercentral.com/articles/Tally+Table/72993/.

    Cross Tabs and Pivots, Part 1 – Converting Rows to Columns - http://www.sqlservercentral.com/articles/T-SQL/63681/
    Cross Tabs and Pivots, Part 2 - Dynamic Cross Tabs - http://www.sqlservercentral.com/articles/Crosstab/65048/
    Understanding and Using APPLY (Part 1) - http://www.sqlservercentral.com/articles/APPLY/69953/
    Understanding and Using APPLY (Part 2) - http://www.sqlservercentral.com/articles/APPLY/69954/

  • Phil's solution sorted it out thanks

  • AllanP999 (3/15/2013)


    Phil's solution sorted it out thanks

    So, your answer on my question was not correct:

    So the answer to your question is I am only interested in rows 1-13 and not any subsequent rows

    as Phil's solution returns rows 16 and 17.

    _____________
    Code for TallyGenerator

Viewing 9 posts - 1 through 8 (of 8 total)

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