Next / previous records from table

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

General SQL Server Forums

New to SQL Server Programming

Next / previous records from table

Author

Topic

M8KWR
Starting Member

26 Posts

Posted - 2008-01-09 : 11:21:00

I have got a table with GUID (PK), COMPANY, CONTACT. There are going to be instances where the company name is the same on multiple records.

What i am trying to do is work out what is the next and previous record

The data is going to be sorted, by COMPANY then GUID.

I am think a stored procedure would be the best to combact this, but very usure how to go about writing it, so i passed the present company and GUID values to it.

Any help would be appreciated, and thanks in advance.

Michael Valentine Jones
Yak DBA Kernel (pronounced Colonel)

7020 Posts

Posted - 2008-01-09 : 11:29:32

Tables do not have next and/or previous rows.

Row order is only defined in the context of a query against a table when you use an ORDER BY clause.

You have to determine the business rules that define next and previous, and write a query based on those rules that returns the data in that order.

CODO ERGO SUM

visakh16
Very Important crosS Applying yaK Herder

52326 Posts

Posted - 2008-01-09 : 11:36:43

Are you using SQL Server 2005? then you can use ROW_NUMBER() function to achieve this:-

something like,

CREATE PROC GetNeighbourCompanies
@Company  varchar(100)
@GUID <datatype>
AS

;
With Company_CTE (RowNo,GUID,COMPANY,CONTACT) As
(
SELECT ROW_NUMBER() OVER(ORDER BY COMPANY,GUID) as 'RowNo',
GUID,
COMPANY,
CONTACT
FROM TABLE
)

SELECT t1.COMPANY AS 'Current',
t2.COMPANY AS 'Previous',
t3.COMPANY AS 'Next'
FROM Company_CTE t1
INNER JOIN Company_CTE t2
ON t2.RowNo=t1.RowNo-1
INNER JOIN Company_CTE t3
ON t3.RowNo=t1.RowNo+1
WHERE t1.COMPANY =@Company
AND t1.GUID=@GUID


GO

Lamprey
Master Smack Fu Yak Hacker

4614 Posts

Posted - 2008-01-09 : 11:41:58

Is the GUID a sequential GUID?

jezemine
Master Smack Fu Yak Hacker

2886 Posts

Posted - 2008-01-09 : 11:53:39

If I understand right, you have a guid, and want to know what the previous and next rows are when you order by that guid? Like this?

-- gets the "previous" row
select top 1 * from yourtable where guid < @guid order by guid desc

-- gets the "next" row
select top 1 * from yourtable where guid > @guid order by guid asc

elsasoft.org

M8KWR
Starting Member

26 Posts

Posted - 2008-01-09 : 12:02:04

i need to order by company name then by GUID, as ordering by guid would not put hte data in ASC order for example...

SO i think i need to include the company name field some where along the lines... but unsure how to...

Many thanks for your help so far.

jezemine
Master Smack Fu Yak Hacker

2886 Posts

Posted - 2008-01-09 : 12:04:44

this?

-- gets the "previous" row
select top 1 * from yourtable where company <= @company and guid < @guid order by company, guid desc

-- gets the "next" row
select top 1 * from yourtable where company >= @company and guid > @guid order by company, guid asc

elsasoft.org

M8KWR
Starting Member

26 Posts

Posted - 2008-01-10 : 02:50:17

The main problem with this is that this assume the GUID is greter or equal... which may not be the case if the company name is different.

Example Data

Company GUID
COMP1 1
COMP1 6
COMP2 2
COMP2 8

If i was on the record with the GUID = 6, and asked for the next record if would give me the record of GUID = 8.

HTH

visakh16
Very Important crosS Applying yaK Herder

52326 Posts

Posted - 2008-01-10 : 02:52:48

did you try my soln?

M8KWR
Starting Member

26 Posts

Posted - 2008-01-10 : 02:58:47

Sorry, i am using 2000, 2005 and mysql... so trying to find a process that will at least deal with MS apps, and then hopefully it may just work with mySQL with a few changes (or that was the plan anyway)

I think i need a stored procedure that i can pass 3 values to - COMPANY, GUID and SQL_String

SQL_String been the where statement the user was able to filter the data by, so the next record the app selects for them would be within their filter.

visakh16
Very Important crosS Applying yaK Herder

52326 Posts

Posted - 2008-01-10 : 03:06:32

quote:
Originally posted by M8KWR

Sorry, i am using 2000, 2005 and mysql... so trying to find a process that will at least deal with MS apps, and then hopefully it may just work with mySQL with a few changes (or that was the plan anyway)

I think i need a stored procedure that i can pass 3 values to - COMPANY, GUID and SQL_String

SQL_String been the where statement the user was able to filter the data by, so the next record the app selects for them would be within their filter.

Do you mean you will input the entire where condition into sp? can you show me how you will call the sp and the result intented?

M8KWR
Starting Member

26 Posts

Posted - 2008-01-10 : 03:17:32

I will try to explain what i am doing and what i want.

I've got a database with 10,000 records, when i users logs into the system they will active a filter, which enables them to see lets say 500. I will have multiple users, all activating different filters, but some may overlap.

When the user click next/previous they then move to the record.

So my thinking was i would need to use the filter (or at least the where statement) in order to determine the next/previous record the system was going to display for them.

So i was think of passing the COMPANY, GUID and lets say WHERE statement, so i would call something like NEXT_RECORD(COMPANY, GUID, WHERE_STATEMENT)

then this passed back the GUIDof the next record only, so then i can find the record using the GUID value quick and easy.

Or this was my thought process. Would you say this is the correct way to go about my probelm.

visakh16
Very Important crosS Applying yaK Herder

52326 Posts

Posted - 2008-01-10 : 03:36:47

I would say it would be better for you create seperate parameters for each of fields against which users will perform filtering nad use this inside the SP(which you can always determine i guess). The current approach will require you to use dynamic sql in your sp and also is vulnerable to sql injection attacks. Then you can use a modified form of sp to perform next previous record search.

CREATE PROC GetNeighbourCompanies
@Company  varchar(100),
@GUID <datatype>,
@Param1 <datatype>=NULL,
@Param2 <datatype>=NULL,
@Param3 <datatype>=NULL,
...
AS

;
With Company_CTE (RowNo,GUID,COMPANY,CONTACT) As
(
SELECT ROW_NUMBER() OVER(ORDER BY COMPANY,GUID) as 'RowNo',
GUID,
COMPANY,
CONTACT
FROM TABLE
WHERE (Field1=@Param1 OR @Param1 IS NULL)
AND (Field2=@Param2 OR @Param2 IS NULL)
AND (Field3=@Param3 OR @Param3 IS NULL)
....
)

SELECT t1.COMPANY AS 'Current',
t2.COMPANY AS 'Previous',
t3.COMPANY AS 'Next'
FROM Company_CTE t1
INNER JOIN Company_CTE t2
ON t2.RowNo=t1.RowNo-1
INNER JOIN Company_CTE t3
ON t3.RowNo=t1.RowNo+1
WHERE t1.COMPANY =@Company
AND t1.GUID=@GUID


GO

M8KWR
Starting Member

26 Posts

Posted - 2008-01-10 : 03:42:34

i am getting an incorrect syntax near the keyword 'AS'.

visakh16
Very Important crosS Applying yaK Herder

52326 Posts

Posted - 2008-01-10 : 04:03:43

quote:
Originally posted by M8KWR

i am getting an incorrect syntax near the keyword 'AS'.

dont put , after last parameter definition. Also remember to put ; before CTE. Can you post query used?

M8KWR
Starting Member

26 Posts

Posted - 2008-01-10 : 04:32:28

I stripped out the pazra values so i could see it working before i entered all of those in...

CREATE PROCEDURE GetNeighbourCompanies
@Company varchar(100),
@GUID varchar(100),

AS

;
With Company_CTE (RowNo,GUID,COMPANY,CONTACT) As
(
SELECT ROW_NUMBER() OVER(ORDER BY COMPANY,ACCOUNTNO) as 'RowNo',
ACCOUNTNO,
COMPANY,
CONTACT
FROM CONTACT1

)

SELECT t1.COMPANY AS 'Current',
t2.COMPANY AS 'Previous',
t3.COMPANY AS 'Next'
FROM Company_CTE t1
INNER JOIN Company_CTE t2
ON t2.RowNo=t1.RowNo-1
INNER JOIN Company_CTE t3
ON t3.RowNo=t1.RowNo+1
WHERE t1.COMPANY =@Company
AND t1.ACCOUNTNO=@GUID

GO

visakh16
Very Important crosS Applying yaK Herder

52326 Posts

Posted - 2008-01-10 : 04:57:07

quote:
Originally posted by M8KWR

I stripped out the pazra values so i could see it working before i entered all of those in...

CREATE PROCEDURE GetNeighbourCompanies
@Company varchar(100),
@GUID varchar(100),

AS

;
With Company_CTE (RowNo,GUID,COMPANY,CONTACT) As
(
SELECT ROW_NUMBER() OVER(ORDER BY COMPANY,ACCOUNTNO) as 'RowNo',
ACCOUNTNO,
COMPANY,
CONTACT
FROM CONTACT1

)

SELECT t1.COMPANY AS 'Current',
t2.COMPANY AS 'Previous',
t3.COMPANY AS 'Next'
FROM Company_CTE t1
INNER JOIN Company_CTE t2
ON t2.RowNo=t1.RowNo-1
INNER JOIN Company_CTE t3
ON t3.RowNo=t1.RowNo+1
WHERE t1.COMPANY =@Company
AND t1.ACCOUNTNO=@GUID

GO

remove the ,

M8KWR
Starting Member

26 Posts

Posted - 2008-01-10 : 05:01:38

I had just noticed....

It works brilliantly. Many thanks... but still got the issue this will not work with 2000...

Do you know if mySQL has got the row_number procedure built in?

LoztInSpace
Aged Yak Warrior

940 Posts

Posted - 2008-01-10 : 20:57:05

quote:
Originally posted by M8KWR

I had just noticed....

It works brilliantly. Many thanks... but still got the issue this will not work with 2000...

Do you know if mySQL has got the row_number procedure built in?

You are considering moving databases because of this feature? Wow. Just Wow.

Anyway - you do not need row_number(). Start your session with GUID as zero. When you need to go forwards, your SP would be
SELECT * from blah WHERE <filter> AND guid=(select max(guid) from table where <filter> and guid > @GUID)

to go backwards, use <

I thin that will work without using ROW_NUMBER. You can use the CTE if you like.

M8KWR
Starting Member

26 Posts

Posted - 2008-01-11 : 03:16:39

i am trying to develope something around 2000 & 2005, without the need to have different code both.

The GIUD are going to be randomly generated characters, so i can not assume they are going to be in the correct order.

visakh16
Very Important crosS Applying yaK Herder

52326 Posts

Posted - 2008-01-11 : 03:36:07

Do you have any audit columns like date of entry or modification in table?

Next Page

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources