SQL Server Performance

issue with Ordering rows in a table while retrieving data

Discussion in 'ALL SQL SERVER QUESTIONS' started by arijitnist, Jan 17, 2013.

  1. arijitnist Member

    Hi,

    I have a table A which has 4 columns UserID int, Time varchar(20), EventType int. Sample data given below

    UserID Time EventType
    1234 9:00 2
    1234 9:03 3
    1234 9:04 2
    1234 9:06 2
    1234 9:05 3
    1234 9:06 3
    1234 9:07 2
    1234 9:08 3
    1234 9:08 2

    I want order the rows by the time in asc order as well as ensure that a eventtype 2 is always followed by 3. I do not have any primary key etc in the table. Also there can be duplicates meaning both eventid 2 and 3 can have the same time associated with them as highligted in the sample. How can i achieve this?

    Output should be

    UserID Time EventType
    1234 9:00 2
    1234 9:03 3
    1234 9:04 2
    1234 9:05 3
    1234 9:06 2
    1234 9:06 3
    1234 9:07 2
    1234 9:08 3
    1234 9:08 2
    1234 9:09 3
    arijitnist, Jan 17, 2013
    #1

  2. FrankKalis Moderator

    Unless I am missing something, you can achieve this by adding an ORDER BY Time, EventTypeID to your query.
    FrankKalis, Jan 17, 2013
    #2

  3. arijitnist Member

    Hi Frank,

    NO this won't give me the desired result as I want the data ordered by Time and also 1 record with EventType 2 should be followed by a record with eventtype 3. Basically eventtype 2 is for start and eventytpe 3 is for stop. Now the problem is the start and stop can have the same Time (please see sample data above). If I order by Time and EventType then I won't get the eventtype 2 followed by eventytpe 3 combination for records having the same time value.
    arijitnist, Jan 18, 2013
    #3

  4. Shehap Moderator

    If So, please try the below query and let me know your feedback

    create table #temp (UserID_Time time,EventType int)

    insert into #temp
    select '9:00', 2
    union select '9:03' ,3
    union select '9:04' ,2
    union select '9:05' ,3
    union select'9:06', 2
    union select'9:06' ,3
    union select'9:07' ,2
    union select'9:08', 3
    union select'9:08' ,2
    union select'9:09', 3

    ;
    with temp as

    ( select row_number ()over ( partition by EventType order by UserID_Time ) rankno ,* from #temp)

    select * from temp order by EventType

    drop table #temp
    Shehap, Jan 18, 2013
    #4

  5. arijitnist Member

    HI Shehap,
    Thank you for your response

    This is not giving me desired result . Please check the output below from your query . Also I am using SQL Server 2005 so time datatype is not there instead the datatype for time is varchar(20) in my db.

    1 09:00:00.0000000 2
    2 09:04:00.0000000 2
    3 09:06:00.0000000 2
    4 09:07:00.0000000 2
    5 09:08:00.0000000 2
    1 09:03:00.0000000 3
    2 09:05:00.0000000 3
    3 09:06:00.0000000 3
    4 09:08:00.0000000 3
    5 09:09:00.0000000 3

    Required output is given below (note eventytpe 2 is followed by eventytpe 3 and time is also sorted. Please check highlighted records where time for 3 is same as the next 2 or it could be vice versa.We have to ensure that 2 is always followed by 3 and also events with the same time are sorted properly .

    09:00:00.0000000 2
    09:03:00.0000000 3
    09:04:00.0000000 2
    09:05:00.0000000 3
    09:05:00.0000000 2
    09:06:00.0000000 3
    arijitnist, Jan 18, 2013
    #5

  6. Shehap Moderator

    Such requirements always need for quirks and can’t be done easily through a single select statements , so I have inspired the below one based on the rich power of partition by clause as shown below:

    create table #temp(UserID_Time time,EventType int)
    create table #temp2(rankno int ,UserID_Time time,EventType int)
    insert into #temp
    select '9:00', 2
    union select '9:03' ,3
    union select '9:04' ,2
    union select '9:05' ,3
    union select'9:06', 2
    union select'9:06' ,3
    union select'9:07' ,2
    union select'9:08', 3
    union select'9:08' ,2
    union select'9:09', 3

    insert into #temp2 select row_number()over ( partition by userid_time order by eventtype desc ) rankno ,* from #temp
    update #temp2 set rankno=1 from #temp2 where exists(select top (1) UserID_Time from #temp where #temp.EventType=#temp2.EventType and #temp.UserID_Time>#temp2.UserID_Time order by UserID_Time ) and rankno=2

    ;
    with FinalOutput as
    (select row_number()over ( partition by rankno,userid_time, eventtype order by eventtype asc ) rankno2 ,* from #temp2)
    select userid_time, eventtype from FinalOutput order by userid_time, rankno2

    drop table #temp
    drop table #temp2

    Please try it and let me know your feedback
    Shehap, Jan 18, 2013
    #6

Share This Page