SELECT "NEXT" - 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 2000 Forums

SQL Server Development (2000)

SELECT "NEXT"

Author

Topic

CLages
Posting Yak Master

116 Posts

Posted - 2003-09-19 : 13:33:11

Hi. I am new here

I have a table like this

Code , invoice, control, date, etc
48 123 17
48 333 01
..
..
49 124 01
49 124 02
49 133 01
49 144 02
...
..
..
as you can see there are a lot of records for each code(customer)
there are a Forms with two buttons NEXT and PRIOR(back)
the user type 49 and click NEXT
I need to get 49 124 01
if he click NEXT again, i get 49 124 02
if he click BACK, i will get 49 124 01 again

and so on

there are no Listview is this forms, each field is shown one by one
I can not use CURSOR, because CURSOR limits a range
ex. if i declare CURSOR where CODE > 49
if User Clicks BACK button, i can get CODE 48.

If I use SELECT * from table where code >= 49
I will get always the same record, if i use
>49 I wil get 50, 51, etc and not 49 124 001, etc

is there a SELEC where i can take one by one ?

tks
Carlos Lages
Rio de Janeiro

tkizer
Almighty SQL Goddess

38200 Posts

Posted - 2003-09-19 : 13:49:18

You can get the next or previous row if you have a column in your table that is numbered sequentially. To do this without modifying your table if you don't have this column, you could put your data into a temporary table that has the first column with an identity value. Then select on the temporary table.

CREATE TABLE #Temp
(
Column1 INT IDENTITY (1, 1) NOT NULL,
Column2 INT NOT NULL
Column3 INT NOT NULL
)

INSERT INTO #Temp (Column2, Column3)
SELECT ColumnA, ColumnB
FROM YourTable

--now let's get the record that he wants
DECLARE @SomeValue INT

SET @SomeValue = 4 -- this is the row that he wants

SELECT Column2, Column3
FROM #Temp
WHERE Column1 = @SomeValue

--to get the previous record
SELECT Column2, Column3
FROM #Temp
WHERE Column1 = @SomeValue - 1

--to get the next record
SELECT Column2, Column3
FROM #Temp
WHERE Column1 = @SomeValue + 1

Tara

CLages
Posting Yak Master

116 Posts

Posted - 2003-09-19 : 14:19:28

tks. but i think this will not work .
let us see why.
1 - the table is to big, create a temporary table every time
the user wants to see some recors, will be to sloww
2 - SELECT Column2, Column3
FROM #Temp
WHERE Column1 = @SomeValue + 1
the value of @somevalue is not changed, then every time
the user click NEXT, i wil get the same record. i will have to manager this.
3 - there are 12 columns to be used in the same situation.

the main ideia is something like this
the use can choose to see the record by
a - Zip code
b - phonenumber
c - ID
d - LastName
etc
but phonenumber and ID there are no problems because one record for each Phone or ID , but ZIpcode , i have problemas because you and me can live in the same street , then i have to show you , me , and everybody who lives in the same adress.

tks
carlos Lages

quote:
Originally posted by tduggan

You can get the next or previous row if you have a column in your table that is numbered sequentially. To do this without modifying your table if you don't have this column, you could put your data into a temporary table that has the first column with an identity value. Then select on the temporary table.

CREATE TABLE #Temp
(
Column1 INT IDENTITY (1, 1) NOT NULL,
Column2 INT NOT NULL
Column3 INT NOT NULL
)

INSERT INTO #Temp (Column2, Column3)
SELECT ColumnA, ColumnB
FROM YourTable

--now let's get the record that he wants
DECLARE @SomeValue INT

SET @SomeValue = 4 -- this is the row that he wants

SELECT Column2, Column3
FROM #Temp
WHERE Column1 = @SomeValue

--to get the previous record
SELECT Column2, Column3
FROM #Temp
WHERE Column1 = @SomeValue - 1

--to get the next record
SELECT Column2, Column3
FROM #Temp
WHERE Column1 = @SomeValue + 1

Tara

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources