Click here to monitor SSC
SQLServerCentral is supported by Red Gate Software Ltd.
 
Log in  ::  Register  ::  Not logged in
 
 
 
        
Home       Members    Calendar    Who's On


Add to briefcase 12»»

Filtering data from a table Expand / Collapse
Author
Message
Posted Wednesday, August 8, 2012 2:42 AM
Forum Newbie

Forum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum Newbie

Group: General Forum Members
Last Login: Wednesday, August 8, 2012 4:22 AM
Points: 5, Visits: 5
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?
Post #1341729
Posted Wednesday, August 8, 2012 2:49 AM


SSCertifiable

SSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiable

Group: General Forum Members
Last Login: Tuesday, September 16, 2014 2:20 AM
Points: 5,216, Visits: 5,106
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?




Want an answer fast? Try here
How to post data/code for the best help - Jeff Moden
Need a string splitter, try this - Jeff Moden
How to post performance problems - Gail Shaw
CrossTabs-Part1 & Part2 - Jeff Moden
SQL Server Backup, Integrity Check, and Index and Statistics Maintenance - Ola Hallengren
Managing Transaction Logs - Gail Shaw
Troubleshooting SQL Server: A Guide for the Accidental DBA - Jonathan Kehayias and Ted Krueger

Post #1341732
Posted Wednesday, August 8, 2012 2:51 AM
Forum Newbie

Forum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum Newbie

Group: General Forum Members
Last Login: Wednesday, August 8, 2012 4:22 AM
Points: 5, Visits: 5
The time column actually contains the time in correct format. I have just given a sample.
Post #1341733
Posted Wednesday, August 8, 2012 2:53 AM


SSCertifiable

SSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiable

Group: General Forum Members
Last Login: Tuesday, September 16, 2014 2:20 AM
Points: 5,216, Visits: 5,106
So time is actually a datetime column?

Can you provide a true representation of the data?




Want an answer fast? Try here
How to post data/code for the best help - Jeff Moden
Need a string splitter, try this - Jeff Moden
How to post performance problems - Gail Shaw
CrossTabs-Part1 & Part2 - Jeff Moden
SQL Server Backup, Integrity Check, and Index and Statistics Maintenance - Ola Hallengren
Managing Transaction Logs - Gail Shaw
Troubleshooting SQL Server: A Guide for the Accidental DBA - Jonathan Kehayias and Ted Krueger

Post #1341735
Posted Wednesday, August 8, 2012 2:57 AM


SSCertifiable

SSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiable

Group: General Forum Members
Last Login: 2 days ago @ 9:50 AM
Points: 5,367, Visits: 9,913
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
Post #1341737
Posted Wednesday, August 8, 2012 2:58 AM


SSC Veteran

SSC VeteranSSC VeteranSSC VeteranSSC VeteranSSC VeteranSSC VeteranSSC VeteranSSC Veteran

Group: General Forum Members
Last Login: Monday, September 8, 2014 2:40 PM
Points: 271, Visits: 1,020
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 is always the hardest
Post #1341738
Posted Wednesday, August 8, 2012 3:00 AM
Forum Newbie

Forum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum Newbie

Group: General Forum Members
Last Login: Wednesday, August 8, 2012 4:22 AM
Points: 5, Visits: 5
Yes, it is. Here is a snapshot of the data:

1 Started 2012-08-07 14:33:35.790
1 Running 2012-08-07 15:41:41.000
1 Completed 2012-08-07 17:51:51.000
2 Started 2012-08-07 04:51:51.000
2 Running 2012-08-07 12:12:31.000
3 Started 2012-08-07 07:12:31.000
Post #1341740
Posted Wednesday, August 8, 2012 3:05 AM


SSCertifiable

SSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiable

Group: General Forum Members
Last Login: Tuesday, September 16, 2014 2:20 AM
Points: 5,216, Visits: 5,106
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





Want an answer fast? Try here
How to post data/code for the best help - Jeff Moden
Need a string splitter, try this - Jeff Moden
How to post performance problems - Gail Shaw
CrossTabs-Part1 & Part2 - Jeff Moden
SQL Server Backup, Integrity Check, and Index and Statistics Maintenance - Ola Hallengren
Managing Transaction Logs - Gail Shaw
Troubleshooting SQL Server: A Guide for the Accidental DBA - Jonathan Kehayias and Ted Krueger

Post #1341742
Posted Wednesday, August 8, 2012 3:11 AM
Forum Newbie

Forum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum Newbie

Group: General Forum Members
Last Login: Wednesday, August 8, 2012 4:22 AM
Points: 5, Visits: 5
Thanks, this is what I wanted
Just a small query. Is this the most efficient/recommended way to achieve the desired output?
Post #1341745
Posted Wednesday, August 8, 2012 3:21 AM


SSCertifiable

SSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiable

Group: General Forum Members
Last Login: Tuesday, September 16, 2014 2:20 AM
Points: 5,216, Visits: 5,106
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.




Want an answer fast? Try here
How to post data/code for the best help - Jeff Moden
Need a string splitter, try this - Jeff Moden
How to post performance problems - Gail Shaw
CrossTabs-Part1 & Part2 - Jeff Moden
SQL Server Backup, Integrity Check, and Index and Statistics Maintenance - Ola Hallengren
Managing Transaction Logs - Gail Shaw
Troubleshooting SQL Server: A Guide for the Accidental DBA - Jonathan Kehayias and Ted Krueger

Post #1341747
« Prev Topic | Next Topic »

Add to briefcase 12»»

Permissions Expand / Collapse