get the previous row - 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 2005 Forums

Transact-SQL (2005)

get the previous row

Author

Topic

sapator
Constraint Violating Yak Guru

462 Posts

Posted - 2009-09-08 : 23:58:51

Hello.
I'm trying to get the previous row on a table

  SELECT seatno, 'prev'
   
      FROM table1
   
      WHERE id < #currentId# 
  
      ORDER BY id DESC
  
      LIMIT 1

I got this error "Incorrect syntax near 'LIMIT'."
If i remove the limit line i get this error "Invalid column name '#currentId#'."

So it this a false way and i have to manually create a row with the previous id in it?

In general, how do i go if i want to get the previous,next row on a query?
Thanks...

Nageswar9
Aged Yak Warrior

600 Posts

Posted - 2009-09-09 : 00:04:55

Hi,

can u post ur table structure and data?

khtan
In (Som, Ni, Yak)

17689 Posts

Posted - 2009-09-09 : 00:48:46

are you using SQL Server 2005 ? LIMIT is not available in MS SQL but is in MySQL


SELECT TOP 1 
      seatno, 'prev'
   
      FROM table1
   
      WHERE id < #currentId# 
  
      ORDER BY id DESC

      LIMIT 1

KH
[spoiler]Time is always against us[/spoiler]

madhivanan
Premature Yak Congratulator

22864 Posts

Posted - 2009-09-09 : 02:29:02

Is #currentId# a variable?

Madhivanan

Failing to plan is Planning to fail

sapator
Constraint Violating Yak Guru

462 Posts

Posted - 2009-09-09 : 10:59:24

Hello, sorry for the late reply.
Yes SQL 2005 and no #currentId# is not a variable.
That's why i'm asking.
My table has one primary id the seatno column and anohter column for some other check.
That's why i asked.Do i have to insert another id column named #currentid#? Is there a predefined variable that will navigate me to the previous row?If not, what do i have to do to get the previous row?
Thanks again...

TG
Master Smack Fu Yak Hacker

6065 Posts

Posted - 2009-09-09 : 11:37:52

sql server doesn't have the concept of a "current" row - so of course it doesn't have previous row, next row etc. What are you trying to accomplish? Are you using an application with a grid to navigate through the table data?

Be One with the Optimizer
TG

sapator
Constraint Violating Yak Guru

462 Posts

Posted - 2009-09-09 : 12:12:33

Actually i'm trying to do a calculation of the previous row with the current row.I suppose it could also be done if i was keeping the value of the previous row in some variable and then bringing it on the current row so i can do a calculation.
Can this be done?

P.S. It can easily be done in Visual Studio but i want it to be stored to a SP so that i don't have the usual delay of Visual Studio getting data from the server.

TG
Master Smack Fu Yak Hacker

6065 Posts

Posted - 2009-09-09 : 13:14:13

You can do a calculation between two rows either for all the rows in the table at once (each row with its previous row conterpart), or for just a single pair of rows. But you need to have some column or columns that define what the sequence is. That would either be an identity column, or perhaps a date column or something that defines the order of the rows so we can tell what the "previous" row is relative to a specific row.

If you want help with it:
script out the statement to Create your table and post it along with the formula for your calculation, and what column(s) define the sequence. Then post what would be the inputs and output structure of the SP.

Be One with the Optimizer
TG

ddramireddy
Yak Posting Veteran

81 Posts

Posted - 2009-09-09 : 13:41:46

you need to pass the current seat no as a parameter to the below query...

SELECT max(seatno), 'prev' FROM table1 WHERE id < @SeatNo

sapator
Constraint Violating Yak Guru

462 Posts

Posted - 2009-09-09 : 19:52:44

Thanks everyone guys.
Now here is what i want to do.
I need a variable for the number of tickets requested and another one for the seat number to start looking for tickets (or i can omit this one), i have another column "status" that if it's "1" then the tickets can be sold.All is ok but the problem is that if a family request tickets (p.e. 4 tickets) then the seats must be close to one another.So if i have seats 4,5,6 open then i have 3 ticks and then i must look for the next possible seats that are 4 together.
So i wanted to calculate the seat that comes in and compare it with the previous seat.If the result is 1 (p.e. status ok seat 10- status ok seat 9 =1 but if seat 9 is not available then seat 10- seat 8 =2 =false)

What i have so far is the table

GO

CREATE TABLE [dbo].[Table1] (
	[id] [bigint] IDENTITY (1, 1) NOT NULL ,
	[seatno] [int] NULL ,
	[status] [int] NULL 
) ON [PRIMARY]
GO

and the script that i try to make with your help....

declare @seatno int
declare @seatrequest int
--- look for seats, will try later
set @seatno =3

---- seat request number
set @seatrequest =3

select  top @seatrequest seatno
-- (SELECT max(seatno) FROM table1 WHERE id < @SeatNo 
-- and status = 1)
from table1
where  ( SeatNo >= @seatno
and status = 1) 
and  seatno-(SELECT max(r.seatno) FROM table1 as r inner join table1 on r.id = table1.id WHERE r.id < r.seatno 
and r.status = 1) = 1  --- check for seat-previous seat = 1
and  status =1

Apart from the problem that i cannot do select top @seatrequest seatno (just replace with p.e. top 100 seatno)
the other problem is that it (obviously) does not work.It returns no value.
So any thoughts (i'll see it tomorrow thought cuz it's 3 in the morning here)?
Thanks again.

sapator
Constraint Violating Yak Guru

462 Posts

Posted - 2009-09-10 : 11:32:03

Hi.
Did anyone have a suggestion?Or i should let visual studio do this work after all.
Thanks again.

TG
Master Smack Fu Yak Hacker

6065 Posts

Posted - 2009-09-10 : 13:23:29

Would it be accurate to describe your overall requirement as:
return <@seatRequest> rows that are the first contiguous block of seats that all have a status = 1?

So if someone says they want 5 seats then you want to find the first 5 seats that are contiguous (unbroken sequence) where all 5 are marked as status=1.

If that is true then do you need to worry about when seat numbers wrap to the next row?

Be One with the Optimizer
TG

sapator
Constraint Violating Yak Guru

462 Posts

Posted - 2009-09-10 : 21:07:42

Mmm...Yes.I was going backwards on the account that if a previous seat is taken then it would have a 2 numbers gap.
But the problem i see is the same as getting the previous row.You must somehow find what the next row number is.
That's why on my first post i also requested the next row.But it seemed more logical to get the previous row since the row had already been passed to the overall rows...

So we are talking for somethnig like this (doesn't work)?

declare @seatno int
declare @seatrequest int
--- look for seats, will try later
set @seatno =3

---- seat request number
set @seatrequest =3

select  top 100 seatno
-- (SELECT max(seatno) FROM table1 WHERE id < @SeatNo 
-- and status = 1)
from table1
where  ( SeatNo < @seatno
and status = 1) 
and  seatno+1 = (SELECT min(r.seatno) FROM table1 as r inner join table1 on r.id+1 = table1.id WHERE r.id > table1.seatno 
and r.status = 1)   --- check for next seat 
and  status =1

maughn
Starting Member

3 Posts

Posted - 2011-05-02 : 04:50:26

i would like to get the previous row field value and paste it in the next row field. can anyone help me on this?

thanks.

khtan
In (Som, Ni, Yak)

17689 Posts

Posted - 2011-05-04 : 02:18:56

quote:
Originally posted by maughn

i would like to get the previous row field value and paste it in the next row field. can anyone help me on this?

thanks.

maughn, it is better you start a new thread for your question. It will get more response

KH
[spoiler]Time is always against us[/spoiler]

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources