SQL Clone
SQLServerCentral is supported by Redgate
 
Log in  ::  Register  ::  Not logged in
 
 
 


Filtering data from a table


Filtering data from a table

Author
Message
manoj.nisal
manoj.nisal
Forum Newbie
Forum Newbie (5 reputation)Forum Newbie (5 reputation)Forum Newbie (5 reputation)Forum Newbie (5 reputation)Forum Newbie (5 reputation)Forum Newbie (5 reputation)Forum Newbie (5 reputation)Forum Newbie (5 reputation)

Group: General Forum Members
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?
anthony.green
anthony.green
SSC-Insane
SSC-Insane (24K reputation)SSC-Insane (24K reputation)SSC-Insane (24K reputation)SSC-Insane (24K reputation)SSC-Insane (24K reputation)SSC-Insane (24K reputation)SSC-Insane (24K reputation)SSC-Insane (24K reputation)

Group: General Forum Members
Points: 24962 Visits: 6519
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
When a question, really isn't a question - Jeff Smith
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


manoj.nisal
manoj.nisal
Forum Newbie
Forum Newbie (5 reputation)Forum Newbie (5 reputation)Forum Newbie (5 reputation)Forum Newbie (5 reputation)Forum Newbie (5 reputation)Forum Newbie (5 reputation)Forum Newbie (5 reputation)Forum Newbie (5 reputation)

Group: General Forum Members
Points: 5 Visits: 5
The time column actually contains the time in correct format. I have just given a sample.
anthony.green
anthony.green
SSC-Insane
SSC-Insane (24K reputation)SSC-Insane (24K reputation)SSC-Insane (24K reputation)SSC-Insane (24K reputation)SSC-Insane (24K reputation)SSC-Insane (24K reputation)SSC-Insane (24K reputation)SSC-Insane (24K reputation)

Group: General Forum Members
Points: 24962 Visits: 6519
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
When a question, really isn't a question - Jeff Smith
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


John Mitchell-245523
John Mitchell-245523
SSC-Dedicated
SSC-Dedicated (35K reputation)SSC-Dedicated (35K reputation)SSC-Dedicated (35K reputation)SSC-Dedicated (35K reputation)SSC-Dedicated (35K reputation)SSC-Dedicated (35K reputation)SSC-Dedicated (35K reputation)SSC-Dedicated (35K reputation)

Group: General Forum Members
Points: 35261 Visits: 16683
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
SGT_squeequal
SGT_squeequal
Ten Centuries
Ten Centuries (1.3K reputation)Ten Centuries (1.3K reputation)Ten Centuries (1.3K reputation)Ten Centuries (1.3K reputation)Ten Centuries (1.3K reputation)Ten Centuries (1.3K reputation)Ten Centuries (1.3K reputation)Ten Centuries (1.3K reputation)

Group: General Forum Members
Points: 1253 Visits: 1118
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 hardestw00t
manoj.nisal
manoj.nisal
Forum Newbie
Forum Newbie (5 reputation)Forum Newbie (5 reputation)Forum Newbie (5 reputation)Forum Newbie (5 reputation)Forum Newbie (5 reputation)Forum Newbie (5 reputation)Forum Newbie (5 reputation)Forum Newbie (5 reputation)

Group: General Forum Members
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
anthony.green
anthony.green
SSC-Insane
SSC-Insane (24K reputation)SSC-Insane (24K reputation)SSC-Insane (24K reputation)SSC-Insane (24K reputation)SSC-Insane (24K reputation)SSC-Insane (24K reputation)SSC-Insane (24K reputation)SSC-Insane (24K reputation)

Group: General Forum Members
Points: 24962 Visits: 6519

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
When a question, really isn't a question - Jeff Smith
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


manoj.nisal
manoj.nisal
Forum Newbie
Forum Newbie (5 reputation)Forum Newbie (5 reputation)Forum Newbie (5 reputation)Forum Newbie (5 reputation)Forum Newbie (5 reputation)Forum Newbie (5 reputation)Forum Newbie (5 reputation)Forum Newbie (5 reputation)

Group: General Forum Members
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?
anthony.green
anthony.green
SSC-Insane
SSC-Insane (24K reputation)SSC-Insane (24K reputation)SSC-Insane (24K reputation)SSC-Insane (24K reputation)SSC-Insane (24K reputation)SSC-Insane (24K reputation)SSC-Insane (24K reputation)SSC-Insane (24K reputation)

Group: General Forum Members
Points: 24962 Visits: 6519
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
When a question, really isn't a question - Jeff Smith
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


Go


Permissions

You can't post new topics.
You can't post topic replies.
You can't post new polls.
You can't post replies to polls.
You can't edit your own topics.
You can't delete your own topics.
You can't edit other topics.
You can't delete other topics.
You can't edit your own posts.
You can't edit other posts.
You can't delete your own posts.
You can't delete other posts.
You can't post events.
You can't edit your own events.
You can't edit other events.
You can't delete your own events.
You can't delete other events.
You can't send private messages.
You can't send emails.
You can read topics.
You can't vote in polls.
You can't upload attachments.
You can download attachments.
You can't post HTML code.
You can't edit HTML code.
You can't post IFCode.
You can't post JavaScript.
You can post emoticons.
You can't post or upload images.

Select a forum

































































































































































SQLServerCentral


Search