June 13, 2012 at 3:49 pm
Hello, I know there are many ways to return data based on the max date. I have data in which there are many records with various dates and I just want to return the person_id and seq_no with the max create_timestamp. Here is some sample data.
person_id seq_no create_timestamp
786B5EA3-53BA-4527-8C4A-8CFB741AB95078162065-3530-4000-BCE2-EC80C74075115/15/2012 15:28:01
786B5EA3-53BA-4527-8C4A-8CFB741AB95094141C29-2865-4678-99BE-EBDB46D36B065/15/2012 14:57:12
786B5EA3-53BA-4527-8C4A-8CFB741AB950EBB8CB78-D5AC-40AB-93D5-092C49D09B9F4/3/2012 15:52:07
786B5EA3-53BA-4527-8C4A-8CFB741AB95013F02E94-5081-4F51-9496-674FE51B90014/2/2012 11:01:42
786B5EA3-53BA-4527-8C4A-8CFB741AB950C0E88E27-F268-48A3-979B-25AA057BAE8E4/1/2012 11:12:56
786B5EA3-53BA-4527-8C4A-8CFB741AB9509404B33D-928D-45C4-A0D7-4FD2202B76B13/14/2012 10:46:53
June 13, 2012 at 3:59 pm
Try this with the appropriate change for your table.
WITH BaseData AS (
SELECT
person_id,
seq_no,
created_timestamp,
ROW_NUMBER() OVER (PARTITION BY person_id ORDER BY created_timestamp DESC) rn
FROM
dbo.yourtablehere
)
SELECT
person_id,
seq_no,
created_timestamp
FROM
BaseData
WHERE
rn = 1;
June 13, 2012 at 4:06 pm
Perfect. Thank you!
David
June 14, 2012 at 1:00 pm
Can this be used in a stored procedure? I am trying to use the following and getting an error:
WITH BaseData AS (
SELECT
person_id,
seq_no,
create_timestamp,
completedDate,
apptDate,
actrecurtimeinterv,
ROW_NUMBER() OVER (PARTITION BY person_id ORDER BY create_timestamp DESC) rn
FROM
dbo.order_
WHERE
actmood = 'RMD'
AND recommendedReason = 'Protocols'
)
SELECT
person_id,
seq_no,
create_timestamp,
completedDate,
apptDate,
actrecurtimeinterv
INTO #ordProtData
FROM
BaseData
WHERE
rn = 1;
I am getting the following error when trying to compile my proc:
Msg 156, Level 15, State 1, Procedure SP_CR_HM_Update_dates_new, Line 77
Incorrect syntax near the keyword 'AS'.
Alone this statement runs perfect.
Thank you,
David
June 14, 2012 at 1:28 pm
One requirement of a CTE is the previous line must end with a semicolon. As such many people have developed the habit of beginning a cte that way.
;with cte as...
Try putting the semicolon in front of your with and it will likely solve that.
_______________________________________________________________
Need help? Help us help you.
Read the article at http://www.sqlservercentral.com/articles/Best+Practices/61537/ for best practices on asking questions.
Need to split a string? Try Jeff Modens splitter http://www.sqlservercentral.com/articles/Tally+Table/72993/.
Cross Tabs and Pivots, Part 1 – Converting Rows to Columns - http://www.sqlservercentral.com/articles/T-SQL/63681/
Cross Tabs and Pivots, Part 2 - Dynamic Cross Tabs - http://www.sqlservercentral.com/articles/Crosstab/65048/
Understanding and Using APPLY (Part 1) - http://www.sqlservercentral.com/articles/APPLY/69953/
Understanding and Using APPLY (Part 2) - http://www.sqlservercentral.com/articles/APPLY/69954/
June 14, 2012 at 1:35 pm
Sean Lange (6/14/2012)
One requirement of a CTE is the previous line must end with a semicolon. As such many people have developed the habit of beginning a cte that way.
;with cte as...
Try putting the semicolon in front of your with and it will likely solve that.
Which actually means terminate the statement prior to the WITH using the semicolon. CTE's don't start with a statement terminator.
June 14, 2012 at 6:26 pm
Sean Lange (6/14/2012)
One requirement of a CTE is the previous line must end with a semicolon. As such many people have developed the habit of beginning a cte that way.
;with cte as...
Try putting the semicolon in front of your with and it will likely solve that.
Not all statements prior to a CTE require termination with a semicolon. Some will work without it. So I usually put it before the WITH to avoid one more error when developing the SQL.
My thought question: Have you ever been told that your query runs too fast?
My advice:
INDEXing a poor-performing query is like putting sugar on cat food. Yeah, it probably tastes better but are you sure you want to eat it?
The path of least resistance can be a slippery slope. Take care that fixing your fixes of fixes doesn't snowball and end up costing you more than fixing the root cause would have in the first place.
Need to UNPIVOT? Why not CROSS APPLY VALUES instead?[/url]
Since random numbers are too important to be left to chance, let's generate some![/url]
Learn to understand recursive CTEs by example.[/url]
[url url=http://www.sqlservercentral.com/articles/St
June 14, 2012 at 7:24 pm
Worked perfect. Thank you!
David
June 14, 2012 at 7:36 pm
dwain.c (6/14/2012)
Sean Lange (6/14/2012)
One requirement of a CTE is the previous line must end with a semicolon. As such many people have developed the habit of beginning a cte that way.
;with cte as...
Try putting the semicolon in front of your with and it will likely solve that.
Not all statements prior to a CTE require termination with a semicolon. Some will work without it. So I usually put it before the WITH to avoid one more error when developing the SQL.
The statement before the CTE may work without the semicolon, but the CTE requires that the previous statement be terminated with a semicolon. As more enhancements are added to SQL Server that require that the statement beterminated with a semicolon (MERGE for example) and others that require that the previous statement be terminated with a semicolon, it just makes sense to get in the habit of terminating all statements with a semicolon.
June 14, 2012 at 7:41 pm
Lynn Pettis (6/14/2012)
dwain.c (6/14/2012)
Sean Lange (6/14/2012)
One requirement of a CTE is the previous line must end with a semicolon. As such many people have developed the habit of beginning a cte that way.
;with cte as...
Try putting the semicolon in front of your with and it will likely solve that.
Not all statements prior to a CTE require termination with a semicolon. Some will work without it. So I usually put it before the WITH to avoid one more error when developing the SQL.
The statement before the CTE may work without the semicolon, but the CTE requires that the previous statement be terminated with a semicolon. As more enhancements are added to SQL Server that require that the statement beterminated with a semicolon (MERGE for example) and others that require that the previous statement be terminated with a semicolon, it just makes sense to get in the habit of terminating all statements with a semicolon.
Lynn - You may be right as the message produced is:
Msg 319, Level 15, State 1, Line 9
Incorrect syntax near the keyword 'with'. If this statement is a common table expression, an xmlnamespaces clause or a change tracking context clause, the previous statement must be terminated with a semicolon.
I could have sworn I've had cases where it wasn't required prior to the CTE but since I always include the semicolon nowadays, I'm at a loss to recall when that was.
Your advice to get into the habit of terminating all statements with a semicolon is sound, its just that old habits die hard.:-)
My thought question: Have you ever been told that your query runs too fast?
My advice:
INDEXing a poor-performing query is like putting sugar on cat food. Yeah, it probably tastes better but are you sure you want to eat it?
The path of least resistance can be a slippery slope. Take care that fixing your fixes of fixes doesn't snowball and end up costing you more than fixing the root cause would have in the first place.
Need to UNPIVOT? Why not CROSS APPLY VALUES instead?[/url]
Since random numbers are too important to be left to chance, let's generate some![/url]
Learn to understand recursive CTEs by example.[/url]
[url url=http://www.sqlservercentral.com/articles/St
Viewing 10 posts - 1 through 9 (of 9 total)
You must be logged in to reply to this topic. Login to reply