Please start any new threads on our new
site at https://forums.sqlteam.com. We've got lots of great SQL Server
experts to answer whatever question you can come up with.
| Author |
Topic |
|
sqldoubt
Starting Member
17 Posts |
 Posted - 2010-06-16 : 17:20:01
|
| How to get Last modified date for a record in a tableI have declare it CREATE TABLE [dbo].[AdCRMMessageprocess_ErrorLog]( [ErrorLogId] [int] IDENTITY(1,1) NOT NULL, [OrderId] [int] NULL, [OrderItemId] [int] NULL, [ErrorMessage] [varchar](max) NULL, [ErrorCode] [varchar](10) NULL, [RetryCount] [int] NULL, [MessageStatus] [int] NULL, [LastProcessedOn] [datetime] NULL, [CreatedOn] [datetime] NULL |
|
|
vijayisonly
Master Smack Fu Yak Hacker
1836 Posts |
Posted - 2010-06-16 : 17:23:05
|
something like thisSELECT * FROM (SELECT ROW_NUMBER() OVER(PARTITION BY OrderId ORDER BY LastProcessedOn DESC) AS SEQ, * FROM [dbo].[AdCRMMessageprocess_ErrorLog]) TWHERE T.SEQ = 1 |
 |
|
|
X002548
Not Just a Number
15586 Posts |
|
|
vijayisonly
Master Smack Fu Yak Hacker
1836 Posts |
Posted - 2010-06-16 : 17:31:30
|
quote:
Originally posted by X002548
oye...what's the obsession with ROW_NUMBERM$ messed up by giving in to the Oracle Denizens...Brett8-)Hint: Want your questions answered fast? Follow the direction in this linkhttp://weblogs.sqlteam.com/brettk/archive/2005/05/25/5276.aspxAdd yourself!http://www.frappr.com/sqlteam
I just thought about it. Most of the solutions I provide involves ROW_NUMBER(). Never realized it till now  . Got to move out out it for sure. |
|
|
|
robvolk
Most Valuable Yak
15732 Posts |
Posted - 2010-06-16 : 17:38:58
|
quote:
M$ messed up by giving in to the Oracle Denizens...
Not exactly, it's part of a suite of windowing functions, and they are pretty damn cool. Once you find a good example you'll get it and wonder how you did without them.Here's a nice number generator:;with a(n) as (select 0 union all select 0 union all select 0),b(n) as (select 0 from a x cross join a y cross join a z),c(n) as (select 0 from b x cross join b y cross join b z)select row_number() over (order by n) n from c Put that into a view and bingo: a numbers/tally table. Uses no storage, is damn fast, and is very easy to expand to any number of rows. |
|
|
|
madhivanan
Premature Yak Congratulator
22864 Posts |
|
|
Lumbago
Norsk Yak Master
3271 Posts |
Posted - 2010-06-17 : 03:17:32
|
| Holy smokes! That was an awesome query rob! With some minor modifications it'll give you all the numbers you need:;with a(n) as (select 0 union all select 0 union all select 0),b(n) as (select 0 from a x cross join a y cross join a z),c(n) as (select 0 from b x cross join b y cross join b z),d(n) as (select 0 from c x cross join c y),e(rownumber) as (select row_number() over (order by n) n from d)select * from e where rownumber <= 10000EDIT: the 10k can be changed to something pretty big if you need to...haven't tried to figure out how much though.- LumbagoMy blog (yes, I have a blog now! just not that much content yet) -> www.thefirstsql.com |
|
|
|
|
|
|
|
|
get Last Modified Date for a record