How to look for the previous and next row in a table

[weefrenchie]

Hi all,

I'm using SQL to access system tables and using Crystal on top of that to run reports.
There's a function in Crystal for "previous" and one for "next" which bring the previous or next record.

Is there a way of doing this in SQL?
I've looked on the web but only found confusing answers...

Thanks !

[code green]

I would imagine that the 'Previous' and 'Next' in Crystal are the next records from a resultset, not the next records in the table.
To simulate this you will need some front end code as SQL has no concept of the order of records in a table

[weefrenchie]

the SQL table has an ID column, although the ID number isn't in sequence for each file, it just increases by 1 for each new record regardless of which file the record is linked to
...does that make sense?

Maybe a formula that would look at the greatest of the ID numbers for each file?

[code green]

It is not clear to me what you are trying to achieve.
If you want to do paging you need to keep in memory the current page.
Then you do a query something like

SELECT TOP 1 FROM table WHERE id = 
(/*here needs to be a value of current_id +- 1 depending on previous or next*/)

Simple enough but where will you store value of current_id.
The query is more commonly

SELECT TOP n FROM table WHERE id (> or < current_id) ORDER BY id

[weefrenchie]

I have a table called [FileHolderAudit] with only a few columns:
[ID]
[Code]
[FileHolderID]
[Date]

the code is a file reference and the FileHolderID is the ID of who owns the file; this changes when the file is passed to another handler

What I need to establish is who is the previous or next handler

So for example file A1 was transferred to Max on 1/12/2009, transferred from Max to Ian on 1/01/2010 and then to Sue on 3/04/2010 - the table will bring something like this:

[ID] [Code] [FileHolderID] [Date]
1234 A1 Max 01/12/2009
7465 A1 Ian 01/01/2010
9745 A1 Sue 03/04/2010

the ID number doesn't follow because the table looks at all files on the system (there's over 50,000) and so each row is added whenever any file is transferred in order of transfer



So what I'm trying to do is work out who the previous handler is on each file
In Crystal I have a formula that looks at who the last handler is (current user) and if it is handler B2, it looks at who was the handler before that, using the previous function of Crystal - but I need to create a column in SQL so I can use it in Crystal

...does that make sense?

[code green]

Is the date column of type DATETIME?
If not can they be converted?
Then you can find id before or after by comparing date.

Can an extra column be inserted using auto-increment?
Then this id would be a linear record of insertion

[weefrenchie]

it is DATETIME

how would you do this though? "Then you can find id before or after by comparing date."

[code green]

SELECT FileHolderID FROM FileHolderAudit 
WHERE code = 'filename'
AND date < 
(SELECT MAX(date) FROM FileHolderAudit 
WHERE ID = current_id
AND code = 'filename')

Is one way.
There are of course three dynamic elements here;
the '<' could also be '>' depending which way you are going
and current_id is the current owner of the file and
filename is the file of interest.

[weefrenchie]

ok, that makes sense
...how do I identify the current_id though?

PS: thanks for your help on this, I've been trying to figure this out all day!

[ck9663]

Going solely based on your sample, what CG means is something like this:

select a.ID, a.code, a.FileHolderID, a.Date,
beforeholder = isnull(select top 1 a.FileHolderID from from FileHolderAudit b where a.code = b.code and a.date > b.date),a.FileHolder),
afterholder = isnull((select top 1 c.FileHolderID from from FileHolderAudit c where a.code = c.code and a.date < c.date),a.FileHolder)
from FileHolderAudit a

I might have messed up the "<" or ">", try experimenting on that one. But I hope you got the idea.

Happy Coding!!!

~~ CK

[code green]

...how do I identify the current_id though

Not sure we need the current_id, I was assuming you already knew that.
Also, as there could be repeat ownership we need an ORDER BY

SELECT TOP 1 FileHolderID FROM FileHolderAudit  
WHERE code = 'filename' 
AND date <  
(SELECT MAX(date) FROM FileHolderAudit
WHERE code = 'filename')  
ORDER BY date ASC

[Jerry Winston]

I know that you know your requirements much better than I, however, I would like to offer you a little advice when using SQL to manage files. I think you should probably have a key field that uniquely identifies each file. By using filename as a key field you will be unable to support scenarios where multiple users have different files with the same name. The solution query posted is effective only if each file has a unique name.

With a key, your audit table looks like this:

[ID] [code] [FileHolderID] [Date]
1234 120 Max 01/12/2009
7465 120 Ian 01/01/2010
9745 120 Sue 03/04/2010


In your tbl_File table, where your file-level detail is stored, the file could be renamed without orphaning your audit history. Another benefit of using a key over the actual filename is index performance. With a filename key, your filename length is bound by character length constraints of the index(900). A separate key field with a uniformed number of bytes will allow filenames of any length without degrading performance.

I hope I have helped. I'll put away my soapbox now. :)

[weefrenchie]

Thanks all for yuor help and advice, this may be a little out of my league...

b0010100 - each file does have an individual code, it's just in a different table; I can link both but I still don't know how to get the previous or next record


code green - thanks for yuor suggestion, however this would only work if I were lookng at one file, as I have to specify the file name - I need a way of identifying the previous or next file holder on each file

I'm kinda new at all this so really grateful for all the help and advice I can get!

[code green]

each file does have an individual code, it's just in a different table

Is the reason we are all struggling because the basic design of your database is flawed?
It may be more helpful to post a new question with the DB structure asking 'if it can be improved'.
You may find it illuminating

[weefrenchie]

Thanks code green, you may be right