Rownumber

  • I have table with Two columns. i.e TIME and ACTION. IT looks like this

    TIME ACTION

    12:40:01 BUY

    12:40:31 BUY

    12:40:51 BUY

    12:41:01 SELL

    12:41:21 SELL

    12:41:31 SELL

    12:41:41 SELL

    12:41:51 BUY

    12:42:01 BUY

    12:42:29 SELL

    12:42:31 SELL

    12:42:41 SELL

    12:42:51 BUY

    I want to remove rows whenever ACTION column with Repetative values.

    After that it should display like this

    TIME ACTION

    12:40:01 BUY

    12:41:01 SELL

    12:41:51 BUY

    12:42:29 SELL

    12:42:51 BUY

    Please help me anyone

  • Already asked and answered here

    http://www.sqlteam.com/forums/topic.asp?TOPIC_ID=102805


    N 56°04'39.16"
    E 12°55'05.25"

  • How about this:

    DECLARE @Actions TABLE

    (

    ActionTime DATETIME

    ,ActionName VARCHAR(10)

    )

    INSERT INTO @Actions

    SELECT '12:40:01' ,'BUY' UNION ALL

    SELECT '12:40:31' ,'BUY' UNION ALL

    SELECT '12:40:51' ,'BUY' UNION ALL

    SELECT '12:41:01' ,'SELL' UNION ALL

    SELECT '12:41:21' ,'SELL' UNION ALL

    SELECT '12:41:31' ,'SELL' UNION ALL

    SELECT '12:41:41' ,'SELL' UNION ALL

    SELECT '12:41:51' ,'BUY' UNION ALL

    SELECT '12:42:01' ,'BUY' UNION ALL

    SELECT '12:42:29' ,'SELL' UNION ALL

    SELECT '12:42:31' ,'SELL' UNION ALL

    SELECT '12:42:41' ,'SELL' UNION ALL

    SELECT '12:42:51' ,'BUY' UNION ALL

    SELECT '01:42:51' ,'BUY'

    SELECT SUBSTRING(CONVERT(VARCHAR(20), A.ActionTime, 108), 1, 5) AS ActionTime

    ,ActionName

    FROM @Actions A

    GROUP BY SUBSTRING(CONVERT(VARCHAR(20), A.ActionTime, 108), 1, 5)

    , ActionName

    If you need to take into account the date as well as the time try this:

    DECLARE @Actions TABLE

    (

    ActionTime DATETIME

    ,ActionName VARCHAR(10)

    )

    INSERT INTO @Actions

    SELECT '01/02/2008 12:40:01' ,'BUY' UNION ALL

    SELECT '01/02/2008 12:40:31' ,'BUY' UNION ALL

    SELECT '01/02/2008 12:40:51' ,'BUY' UNION ALL

    SELECT '01/02/2008 12:41:01' ,'SELL' UNION ALL

    SELECT '01/03/2008 12:41:21' ,'SELL' UNION ALL

    SELECT '01/03/2008 12:41:31' ,'SELL' UNION ALL

    SELECT '01/02/2008 12:41:41' ,'SELL' UNION ALL

    SELECT '01/02/2008 12:41:51' ,'BUY' UNION ALL

    SELECT '01/02/2008 12:42:01' ,'BUY' UNION ALL

    SELECT '01/02/2008 12:42:29' ,'SELL' UNION ALL

    SELECT '01/02/2008 12:42:31' ,'SELL' UNION ALL

    SELECT '01/02/2008 12:42:41' ,'SELL' UNION ALL

    SELECT '01/02/2008 12:42:51' ,'BUY' UNION ALL

    SELECT '01/02/2008 01:42:51' ,'BUY'

    SELECT CONVERT(VARCHAR(20), A.ActionTime, 112) AS ActionDate

    ,SUBSTRING(CONVERT(VARCHAR(20), A.ActionTime, 108), 1, 5) AS ActionTime

    ,ActionName

    FROM @Actions A

    GROUP BY CONVERT(VARCHAR(20), A.ActionTime, 112)

    ,SUBSTRING(CONVERT(VARCHAR(20), A.ActionTime, 108), 1, 5)

    , ActionName

  • I'm hoping you screwed up your sample again, because if you didn't - you're SOL. (you'd be wanting us to rely on "physical order" to break these things, which isn't something that has any "meaning" in a table on SQL server).

    On the other hand - if you're trying to find the changes in actions based on the time component, you could try this (reusing ggraber's test setup):

    ;with cte1 as (

    select ROW_NUMBER() over (order by ActionTime) as rn,

    ActionTime,

    ActionName

    from @actions)

    select c1.ActionTime, c1.actionName

    from cte1 c1

    left outer join cte1 c2 on c1.rn=c2.rn-1

    where c1.actionName <> isnull(c2.actionName,'')

    ----------------------------------------------------------------------------------
    Your lack of planning does not constitute an emergency on my part...unless you're my manager...or a director and above...or a really loud-spoken end-user..All right - what was my emergency again?

  • Matt, your example misses a row.

    My example returns one row for each minute, yours misses one minute.

    Your Results:

    ActionTimeactionName

    1900-01-01 12:40:51.000BUY

    1900-01-01 12:41:41.000SELL

    1900-01-01 12:42:01.000BUY

    1900-01-01 12:42:41.000SELL

    1900-01-01 12:42:51.000BUY

    My Results:

    ActionTimeActionName

    01:42BUY

    12:40BUY

    12:41BUY

    12:41SELL

    12:42BUY

    12:42SELL

  • hmm - that's strange - because I get 6 rows, not 5.

    ActionTimeactionName

    2008-01-02 12:40:51.000BUY

    2008-01-02 12:41:41.000SELL

    2008-01-02 12:42:01.000BUY

    2008-01-02 12:42:41.000SELL

    2008-01-02 12:42:51.000BUY

    2008-01-03 12:41:31.000SELL

    It might make a difference that your SECOND definition of actions returns different results than the first.....

    Also - sameer isn't doing us any favors here, since my understanding is that he only wants to know when the actions "switch" from buy to sell and back, and not have repeating BUY rows (like in your solution). That's not so much a "miss" as a "on purpose". I don't "care" about the minutes at all.

    Again - without knowing more details, hard to tell which is supposed to be "right".

    ----------------------------------------------------------------------------------
    Your lack of planning does not constitute an emergency on my part...unless you're my manager...or a director and above...or a really loud-spoken end-user..All right - what was my emergency again?

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

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