Help with Query - SQL Server Forums

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.

All Forums

SQL Server 2008 Forums

Transact-SQL (2008)

Help with Query

Author

Topic

xpandre
Posting Yak Master

212 Posts

Posted - 2015-01-26 : 22:58:19

sql server : 2008
create table a (id int, status varchar(100), created_date datetime)
go

insert into a(id, status, created_date)
select 1,'post',getdate()
union all
select 1,'confirm',dateadd(mi,1,getdate())
union all
select 1,'error',dateadd(mi,2,getdate())
union all
select 1,'resend',dateadd(mi,3,getdate())
union all
select 2,'post',getdate()
union all
select 2,'confirm',dateadd(mi,4,getdate())
union all
select 2,'error',dateadd(mi,5,getdate())
select * from a

output I want is:
for ID 1, I have a status of error, and after that it was resend..so this should not be returned.
for ID 2, I have a status of error, and after that it was not resend..so this should be returned.

The SP input is nothing. We always need to check for the error status and if any other message was posted after error. If yes, then ignore it, else return it.

I tried doing
getting all id's with "error" status into a temp table
then partition it with row_number order by create date
and if row number = 1 and status = error, return else do not return

But this solution would not be good enough when the data grows:-(

AASC
Starting Member

24 Posts

Posted - 2015-01-27 : 02:56:51

You do not need to create and dump data into Temp table, use derived table instead.

Also create a NonClustered index on table and apply DESC sort on created_date.

CREATE NONCLUSTERED INDEX [IX_ID_CreatedDate] ON [dbo].[a]
(
[id] ASC,
[created_date] DESC
)
INCLUDE ( [status]) WITH (PAD_INDEX = OFF, STATISTICS_NORECOMPUTE = OFF, SORT_IN_TEMPDB = OFF, IGNORE_DUP_KEY = OFF, DROP_EXISTING = OFF, ONLINE = OFF, ALLOW_ROW_LOCKS = ON, ALLOW_PAGE_LOCKS = ON) ON [PRIMARY]
GO

select *
from
(
select row_number()over(partition by id order by id,created_date desc) RID,*
from a Outa
)a where RID=1 and status='error'

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources