Previous result for multiple results

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 2005 Forums

Transact-SQL (2005)

Previous result for multiple results

Author

Topic

ranalk
Starting Member

49 Posts

Posted - 2010-10-25 : 11:36:22

Hi,

I having hard time finding the previous record from a given table of LogId, UTCDate and SessionId.

The values are taken from simple temp table with 2 columns :LogId, UTCDate, SessionId.

The result that I need is from a log table and I need only the previous record to the one shown on the temp table.
For example if the temp table hold a record:
"100,2010-10-25 10:58:50.743, General error " then I need the one before from the log table - "99,2010-10-25 10:57:00.000, Login ok"

Thanks in advance.

webfred
Master Smack Fu Yak Hacker

8781 Posts

Posted - 2010-10-25 : 13:22:29

select l.*
from logtable as l
join temptable t
on t.id=l.id+1

No, you're never too old to Yak'n'Roll if you're too young to die.

jimf
Master Smack Fu Yak Hacker

2875 Posts

Posted - 2010-10-25 : 13:27:56

declare @log varchar(1000)
set @log = '100,2010-10-25 10:58:50.743, General error'
declare @id int
set @id = substring(@log,1,charindex(',',@log)-1) -1
select @id

If you can be more specific with what your actual needs are you can get a more appropriate answer.

Jim

Everyday I learn something that somebody else already knew

ranalk
Starting Member

49 Posts

Posted - 2010-10-26 : 03:04:45

Well my main problem is that I need the previous result for each session.
So my dependence are on two values SessionId and LogId.

e.g. these are the records that I need to find the previous record for each records :

Uploaded with [URL=http://imageshack.us]ImageShack.us[/URL]

As the log records all traffic I need to use the session and the LogID in order to retrieve the previous record for each record.

I deeply need help.

ranalk
Starting Member

49 Posts

Posted - 2010-10-26 : 05:19:36

Could someone help on this ?

webfred
Master Smack Fu Yak Hacker

8781 Posts

Posted - 2010-10-26 : 05:57:01

You are talking about two tables - one temp and one other table.
Give DDL, sample data and wanted output and I am sure someone will come up with a solution.

No, you're never too old to Yak'n'Roll if you're too young to die.

ranalk
Starting Member

49 Posts

Posted - 2010-10-26 : 06:53:55

That's why I added the screeshot.
It is enough to focus on these 3 column the syslogid is the table Key.
All sessions are writing to the same table, I need the previous record for each shown in my temp table.
If we will take the first record on the screenshot then i need the previous record which is the top 1 that less then 152275090 and belong to to the same sessionid.

webfred
Master Smack Fu Yak Hacker

8781 Posts

Posted - 2010-10-26 : 07:30:47

But how do we know where to start if each row can have a previous row?
Is it for example the row with the highest syslogid group by sessionid?

No, you're never too old to Yak'n'Roll if you're too young to die.

ranalk
Starting Member

49 Posts

Posted - 2010-10-26 : 07:58:54

The SyslogID on the temp table is the reference point.
As was mentioned in my previous example I need the closest syslogid to 152275090 in the same session.
I can find it for a single record, but I'm have hard time to do it on multiple results.
here is the script for single record:

SELECT * FROM Syslogd gs
WHERE
gs.SysLogID in (SELECT TOP 1 SysLogID
FROM Syslogd gs
WHERE gs.MsgSessionID='dmdvjkgtnwy445srtza545'
AND gs.SysLogID<152275090
ORDER BY gs.SysLogID desc )

ranalk
Starting Member

49 Posts

Posted - 2010-10-27 : 03:00:50

Anyone can help me resolve this puzzle ?

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources