MS SQL

How to get the previous row for a selection criteria

Please support our MS SQL advertiser: Intel Parallel Studio Home

•

Join Date: Jan 2009

Posts: 2

Reputation: guest_user is an unknown quantity at this point

Solved Threads: 0

guest_user guest_user is offline

Offline

Newbie Poster

How to get the previous row for a selection criteria

Jan 22nd, 2009

We have a Log table which logs the events of a request.
When any error event occurs, I need a query to see the row with errorevent and the previous row to this row which contains the actual data which caused the error.

Here is the table structure:
LOGID (int), --> primary key
EventId (int),
Description (varchar (max))
Logtime (datetime)

Any help is greatly appreciated..
Thanks

•

Join Date: Aug 2006

Posts: 2,065

Reputation: Ramy Mahrous is just really nice

Solved Threads: 256

Ramy Mahrous Ramy Mahrous is offline

Offline

Postaholic

Re: How to get the previous row for a selection criteria

Jan 22nd, 2009

Give me sample data and what you need to get, then I can understand you well

BI Developer | LINKdotNET
B.Sc Computer Science, Helwan University
Technical blog | http://ramymahrous.wordpress.com
LinkedIn | http://www.linkedin.com/in/ramymahrous

•

Join Date: Jan 2009

Posts: 2

Reputation: guest_user is an unknown quantity at this point

Solved Threads: 0

guest_user guest_user is offline

Offline

Newbie Poster

Re: How to get the previous row for a selection criteria

Jan 22nd, 2009

•

Originally Posted by RamyMahrous

Give me sample data and what you need to get, then I can understand you well

when I a get a request, we log the event with eventid =1 and request data in the description field
In the next step, we validate field in the the request and log an errorevent with eventid=4 and the error message as description .
Now I want to see the description data (which contains actual request data) from the previous row to the errorevent record.
Here is the sample data:

•

Join Date: Aug 2006

Posts: 2,065

Reputation: Ramy Mahrous is just really nice

Solved Threads: 256

Ramy Mahrous Ramy Mahrous is offline

Offline

Postaholic

Re: How to get the previous row for a selection criteria

Jan 22nd, 2009

•

Here is the sample data:

???!

BI Developer | LINKdotNET
B.Sc Computer Science, Helwan University
Technical blog | http://ramymahrous.wordpress.com
LinkedIn | http://www.linkedin.com/in/ramymahrous

•

Join Date: Jun 2005

Posts: 60

Reputation: pclfw is an unknown quantity at this point

Solved Threads: 5

pclfw

Offline

Junior Poster in Training

Re: How to get the previous row for a selection criteria

Jan 23rd, 2009

I am making an assumption or two here, such as LOGID, your primary key, is an IDENTITY column or similar, so try this

 Help with Code Tags 
 SQL Syntax (Toggle Plain Text) 
SELECT e.logid, e.eventid, e.description, e.logtime,
 l.logid, l.eventid, l.description, l.logtime
FROM logtable e, logtable l
WHERE e.eventid = <event id that you want to examine>
AND l.logid = (e.logid - 1)
ORDER BY e.logtime DESC, e.logid DESC
SELECT e.logid, e.eventid, e.description, e.logtime,
 l.logid, l.eventid, l.description, l.logtime
FROM logtable e, logtable l
WHERE e.eventid = <event id that you want to examine>
and l.logid = (e.logid - 1)
ORDER by e.logtime DESC, e.logid DESC

Add another AND clause

 Help with Code Tags 
 SQL Syntax (Toggle Plain Text) 
AND e.logtime = (SELECT max(logtime) FROM logtable WHERE eventid = <event id that you want to examine>
AND e.logtime = (select max(logtime) from logtable where eventid = <event id that you want to examine>

to get only the latest event records for the relevant event code.