SELECT/COUNT CONSECUTIVE NUMBERS

  • 1) How can I select only the first record in a series of certain consecutive numbers?

    2) How can I obtain the count for the number of times a set of those certain consecutive numbers appear?

    Example: (this assumes an un-altered Northwind database)

    USE Northwind

    GO

    WITH Peter AS

    (SELECT EmployeeID, Freight,

    ROW_NUMBER() OVER(PARTITION BY EmployeeID ORDER BY Freight) AS [RecordNumber]

    FROM Orders)

    SELECT * FROM Peter

    Partial Result Set

    EmployeeID   Freight   RecordNumber

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

    1                   0.21       1

    1                   0.45       2

    1                   0.93       3

    1                   1.27       4

    1                   1.35       5

    1                   1.36       6

    1                   1.51       7

    1                   1.66       8

    1                   2.50       9

    1                   3.94       10

    1                   4.03       11

    1                   4.27       12

    1                   4.41       13

    1                   4.98       14

    1                   4.99       15

    1                   4.99       16

    1                   7.46       17

    From the list, records 5 and 6 show consecutive Freight figures of 1.35 and 1.36 for EmployeeID 1.

    From the list, records 14 and 15 show consecutive Freight figures of 4.98 and 4.99 for EmployeeID 1. (Ignore duplicate Freight figures for this question)

    In this example how could I get a result set that only showed records, 1.35 and 4.98 - that is the first record in a series of consecutive Freight figures?

    How could I get a count of the number of consecutive records for each set?

    A partial result set should look like this.

    EmployeeID   1stFreightRecord   Count

    1                   1.35                     2

    1                   4.98                     2

  • I have already posted the result in http://www.sqlservercentral.com/forums/shwmessage.aspx?forumid=8&messageid=367397

    Maybe it is better not to post the same question in 2 threads.

    Jan

  • Jan,

    Thanks again for trying. However, your "result" does match the result set sought after in my post as shown in the last few lines. Your results lack the count. In addition, the request states to ignore duplicates. Your result shows a duplicate.

    Maybe it is better to post the same question in 3 threads.

  • I replied on:

    http://www.sqlservercentral.com/forums/shwmessage.aspx?forumid=8&messageid=367397

    I think most users would agree that it is annoying to post the same question in several threads. This is not helpful for other people who are reading this and who can possibly evaluate and correct my tries. We are trying to help you.

  • I would agree that it would be annoying if I posted this question twice in this forum, but I didn't. This site has two forums for T-SQL. One for SQL 2005 and one for SQL 2000/7 (Perhaps you find that annoying). The question was posted in the same manner - once in each forum for T-SQL. My posting of the question is consistent with the site's design.

  • SOLUTION

    SELECT

    EmployeeID

    ,LowFreight

    ,(

    SELECT count(*) FROM Orders PS

    WHERE PS.EmployeeID = T.EmployeeID AND (PS.Freight BETWEEN T.LowFreight AND T.HighFreight)

    ) as [SeriesRowCount]

    FROM

    (

    SELECT EmployeeID, Freight as LowFreight

    ,(

    SELECT min(Freight) FROM Orders PS3

    WHERE EXISTS

    (

    SELECT * FROM Orders PS2

    WHERE ( PS2.EmployeeID = PS3.EmployeeID AND PS2.Freight = (PS3.Freight - .01) )

    )

    AND NOT EXISTS

    (

    SELECT * FROM Orders PS2

    WHERE ( PS2.EmployeeID = PS3.EmployeeID AND PS2.Freight = (PS3.Freight + .01) )

    )

    AND PS3.EmployeeID = PS1.EmployeeID

    AND PS3.Freight > PS1.Freight

    ) as HighFreight

    FROM Orders PS1

    WHERE EXISTS

    (

    SELECT * FROM Orders PS2

    WHERE ( PS2.EmployeeID = PS1.EmployeeID AND PS2.Freight = (PS1.Freight + .01) )

    )

    AND NOT EXISTS

    (

    SELECT * FROM Orders PS2

    WHERE ( PS2.EmployeeID = PS1.EmployeeID AND PS2.Freight = (PS1.Freight - .01) )

    )

    ) T

    ORDER BY EmployeeID, LowFreight

  • Are you using SQL 2000 or SQL 2005? If you are using 2000, post in the 2000 forums. If you are using 2005, please post in the 2005 forums.

    Most of the people who answer the questions use the active threads, so we see all active discussins.

    Gail Shaw
    Microsoft Certified Master: SQL Server, MVP, M.Sc (Comp Sci)
    SQL In The Wild: Discussions on DB performance with occasional diversions into recoverability

    We walk in the dark places no others will enter
    We stand on the bridge and no one may pass
  • both

  • cross posting is a no-no.

    it may not be expressly forbidden by the site's rules, but there's no denying that it is bad form.  ask any regular here or any other similar site and you'll get a similar opinion.

    it wastes the time of people that are trying to help you, since someone could spend time coming up with an answer when it's already been answered elsewhere.

    ---------------------------------------
    elsasoft.org

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

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