August 8, 2012 at 2:42 am
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?
August 8, 2012 at 2:49 am
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?
August 8, 2012 at 2:51 am
The time column actually contains the time in correct format. I have just given a sample.
August 8, 2012 at 2:53 am
So time is actually a datetime column?
Can you provide a true representation of the data?
August 8, 2012 at 2:57 am
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
August 8, 2012 at 2:58 am
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 *******
August 8, 2012 at 3:00 am
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
August 8, 2012 at 3:05 am
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
August 8, 2012 at 3:11 am
Thanks, this is what I wanted 🙂
Just a small query. Is this the most efficient/recommended way to achieve the desired output?
August 8, 2012 at 3:21 am
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.
August 8, 2012 at 3:26 am
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