Filtering data from a table

  • I have a table which contains data somewhat in following manner:

    ID Status Time

    1 Started 7:00

    1 Running 8:30

    1 Completed 9:30

    2 Started 4:15

    2 Running 5:00

    Here there can be multiple rows corresponding to a single ID. I want to get the latest status for all the IDs.

    So the output should be:

    ID Status Time

    1 Completed 9.30

    2 Running 5:00

    What is the best way to get this output? Do we need to iterate through individual records and then insert into some other temporary table?

  • what happens should the process run over multiple days

    eg.

    Process 1 starts at 08/08/2012 23:50

    Process 1 finishes at 09/08/2012 00:10

    how do you differentiate what is yesterday and what is tomorrow?

  • The time column actually contains the time in correct format. I have just given a sample.

  • So time is actually a datetime column?

    Can you provide a true representation of the data?

  • No, you don't need to iterate or use temporary tables. All you need is a fairly simple GROUP BY query. Please give it a try, and post back if you come up against any particular problems. If you do so, please supply sample data and DDL in the way described in the second link in Anthony's signature above.

    John

  • have a look at this

    create table #t1 (id int, status varchar(10), tme time)

    insert into #t1 select 1,'started','07:00' union all

    select 1,'running','08:00' union all

    select 1,'completed','09:00' union all

    select 2,'started','10:00' union all

    select 2,'running','11:00'

    ;with cts

    as (

    select id,max(tme) tm from #t1

    group by id)

    select a.* from #t1 a inner join

    cts b on a.id=b.id and tm=tme

    order by a.id

    ***The first step is always the hardest *******

  • Yes, it is. Here is a snapshot of the data:

    1Started 2012-08-07 14:33:35.790

    1Running 2012-08-07 15:41:41.000

    1Completed 2012-08-07 17:51:51.000

    2Started2012-08-07 04:51:51.000

    2Running2012-08-07 12:12:31.000

    3Started2012-08-07 07:12:31.000

  • declare @sometable table (ID int, Status nvarchar(50), [time] datetime)

    insert into @sometable values (1,'Started','2012-02-02 07:00'),

    (1,'Running','2012-02-02 08:30'),

    (1,'Completed','2012-02-02 09:30'),

    (2,'Started','2012-02-02 04:15'),

    (2,'Running','2012-02-02 05:00')

    ;with cte as

    (

    SELECT

    ROW_NUMBER() OVER(PARTITION BY ID ORDER BY DT DESC) AS RowNum,

    *

    FROM

    @SomeTable

    )

    SELECT

    ID,

    Status,

    Time

    FROM

    CTE

    WHERE

    RowNum = 1

    ORDER BY

    ID

  • Thanks, this is what I wanted 🙂

    Just a small query. Is this the most efficient/recommended way to achieve the desired output?

  • Yeah its fairly speedy that.

    Another option to do it based on my sample data is

    SELECT

    t1.ID,

    t1.Status,

    t1.Time

    FROM

    @sometable t1

    INNER JOIN

    (

    SELECT

    ID,

    MAX(Time) as maxtime

    FROM

    @sometable t2

    GROUP BY

    ID

    ) as dev1

    ON

    t1.ID = dev1.ID

    AND

    t1.time = dev1.maxtime

    ORDER BY

    1

    Running both CTE and the above join method in the same query batch, the CTE comes back as a 36% cost and the join method 64% cost.

    But without your full table definition indexes and full data we can only speculate as to if its the best option for you or not.

  • Thanks a lot Anthony 🙂

Viewing 11 posts - 1 through 10 (of 10 total)

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