Query Without Cursor

I gave read a lot about cursors being inefficent and i would like to re-write
the below query without using them, but not sure how i would do this, sure
there will be a way to do this, but not sure how, can anyone help:
The query baisically sets the the linenum on a hoiday itinerary, based on
date:
----
declare @.cnt integer
set @.cnt = 2
declare cursorItin cursor fast_forward
FOR SELECT [id]
FROM dbo.Itinerary
WHERE bkref=@.bkref AND linenum<>1
Order By itinDateTime
open cursorItin
fetch next from cursorItin into @.id
while @.@.fetch_status=0
begin
UPDATE dbo.Itinerary
SET linenum=@.cnt
WHERE id=@.id
set @.cnt = @.cnt + 1
fetch next from cursorItin into @.id
end
close cursorItin
deallocate cursorItin
---On 14 Jun, 10:08, Billy <B...@.discussions.microsoft.com> wrote:
> I gave read a lot about cursors being inefficent and i would like to re-write
> the below query without using them, but not sure how i would do this, sure
> there will be a way to do this, but not sure how, can anyone help:
> The query baisically sets the the linenum on a hoiday itinerary, based on
> date:
> ----
> declare @.cnt integer
> set @.cnt = 2
> declare cursorItin cursor fast_forward
> FOR SELECT [id]
> FROM dbo.Itinerary
> WHERE bkref=@.bkref AND linenum<>1
> Order By itinDateTime
> open cursorItin
> fetch next from cursorItin into @.id
> while @.@.fetch_status=0
> begin
> UPDATE dbo.Itinerary
> SET linenum=@.cnt
> WHERE id=@.id
> set @.cnt = @.cnt + 1
> fetch next from cursorItin into @.id
> end
> close cursorItin
> deallocate cursorItin
> ---
Try:
WITH t AS
(SELECT id, linenum,
ROW_NUMBER() OVER (PARTITION BY bkref ORDER BY itinDateTime, id)
AS new_linenum
FROM Itinerary)
UPDATE t SET linenum = new_linenum;
--
David Portas, SQL Server MVP
Whenever possible please post enough code to reproduce your problem.
Including CREATE TABLE and INSERT statements usually helps.
State what version of SQL Server you are using and specify the content
of any error messages.
SQL Server Books Online:
http://msdn2.microsoft.com/library/ms130214(en-US,SQL.90).aspx
--|||Apologies, should have mentioned before am running on SQL 2000, sp4.
I am getting error:
"'ROW_NUMBER' is not a recognized function name"
Is ROW_NUMBER a 2005 only function?
"David Portas" wrote:
> On 14 Jun, 10:08, Billy <B...@.discussions.microsoft.com> wrote:
> > I gave read a lot about cursors being inefficent and i would like to re-write
> > the below query without using them, but not sure how i would do this, sure
> > there will be a way to do this, but not sure how, can anyone help:
> >
> > The query baisically sets the the linenum on a hoiday itinerary, based on
> > date:
> >
> > ----
> > declare @.cnt integer
> > set @.cnt = 2
> >
> > declare cursorItin cursor fast_forward
> > FOR SELECT [id]
> > FROM dbo.Itinerary
> > WHERE bkref=@.bkref AND linenum<>1
> > Order By itinDateTime
> >
> > open cursorItin
> > fetch next from cursorItin into @.id
> > while @.@.fetch_status=0
> > begin
> > UPDATE dbo.Itinerary
> > SET linenum=@.cnt
> > WHERE id=@.id
> > set @.cnt = @.cnt + 1
> > fetch next from cursorItin into @.id
> > end
> > close cursorItin
> > deallocate cursorItin
> > ---
> Try:
> WITH t AS
> (SELECT id, linenum,
> ROW_NUMBER() OVER (PARTITION BY bkref ORDER BY itinDateTime, id)
> AS new_linenum
> FROM Itinerary)
> UPDATE t SET linenum = new_linenum;
> --
> David Portas, SQL Server MVP
> Whenever possible please post enough code to reproduce your problem.
> Including CREATE TABLE and INSERT statements usually helps.
> State what version of SQL Server you are using and specify the content
> of any error messages.
> SQL Server Books Online:
> http://msdn2.microsoft.com/library/ms130214(en-US,SQL.90).aspx
> --
>|||On 14 Jun, 11:31, Billy <B...@.discussions.microsoft.com> wrote:
> Apologies, should have mentioned before am running on SQL 2000, sp4.
> I am getting error:
> "'ROW_NUMBER' is not a recognized function name"
> Is ROW_NUMBER a 2005 only function?
>
ROW_NUMBER() was introduced in 2005.
You didn't post any CREATE TABLE statement so I'll have to make a
guess at what the key of your table is. I'll assume that the
combination of (bkref, itinDateTime, ID) is unique:
UPDATE Itinerary SET linenum =(SELECT COUNT(*)
FROM Itinerary AS I
WHERE bkref = Itinerary.bkref
AND
(itinDateTime < Itinerary.itinDateTime
OR
(id <= Itinerary.id
AND itinDateTime = Itinerary.itinDateTime)
)
);
If you need more help, please post a CREATE TABLE statement (including
constraints), some INSERT statements consisting of some sample data
and then show what end result you want from that data.
--
David Portas, SQL Server MVP
Whenever possible please post enough code to reproduce your problem.
Including CREATE TABLE and INSERT statements usually helps.
State what version of SQL Server you are using and specify the content
of any error messages.
SQL Server Books Online:
http://msdn2.microsoft.com/library/ms130214(en-US,SQL.90).aspx
--|||Yes, ROW_NUMBER doesn't seems to exist in SQL2000
--
Regards
Steen Schlüter Persson
Database Administrator / System Administrator
Billy wrote:
> Apologies, should have mentioned before am running on SQL 2000, sp4.
> I am getting error:
> "'ROW_NUMBER' is not a recognized function name"
> Is ROW_NUMBER a 2005 only function?
> "David Portas" wrote:
>> On 14 Jun, 10:08, Billy <B...@.discussions.microsoft.com> wrote:
>> I gave read a lot about cursors being inefficent and i would like to re-write
>> the below query without using them, but not sure how i would do this, sure
>> there will be a way to do this, but not sure how, can anyone help:
>> The query baisically sets the the linenum on a hoiday itinerary, based on
>> date:
>> ----
>> declare @.cnt integer
>> set @.cnt = 2
>> declare cursorItin cursor fast_forward
>> FOR SELECT [id]
>> FROM dbo.Itinerary
>> WHERE bkref=@.bkref AND linenum<>1
>> Order By itinDateTime
>> open cursorItin
>> fetch next from cursorItin into @.id
>> while @.@.fetch_status=0
>> begin
>> UPDATE dbo.Itinerary
>> SET linenum=@.cnt
>> WHERE id=@.id
>> set @.cnt = @.cnt + 1
>> fetch next from cursorItin into @.id
>> end
>> close cursorItin
>> deallocate cursorItin
>> ---
>> Try:
>> WITH t AS
>> (SELECT id, linenum,
>> ROW_NUMBER() OVER (PARTITION BY bkref ORDER BY itinDateTime, id)
>> AS new_linenum
>> FROM Itinerary)
>> UPDATE t SET linenum = new_linenum;
>> --
>> David Portas, SQL Server MVP
>> Whenever possible please post enough code to reproduce your problem.
>> Including CREATE TABLE and INSERT statements usually helps.
>> State what version of SQL Server you are using and specify the content
>> of any error messages.
>> SQL Server Books Online:
>> http://msdn2.microsoft.com/library/ms130214(en-US,SQL.90).aspx
>> --
>>|||that works great, thanks david, i just need to understand it now! (scratches
head)
I guess the subquery almost works as a loop returning the number of rows
whose date is less than the previous row?
"David Portas" wrote:
> On 14 Jun, 11:31, Billy <B...@.discussions.microsoft.com> wrote:
> > Apologies, should have mentioned before am running on SQL 2000, sp4.
> >
> > I am getting error:
> > "'ROW_NUMBER' is not a recognized function name"
> >
> > Is ROW_NUMBER a 2005 only function?
> >
> >
> ROW_NUMBER() was introduced in 2005.
> You didn't post any CREATE TABLE statement so I'll have to make a
> guess at what the key of your table is. I'll assume that the
> combination of (bkref, itinDateTime, ID) is unique:
> UPDATE Itinerary SET linenum => (SELECT COUNT(*)
> FROM Itinerary AS I
> WHERE bkref = Itinerary.bkref
> AND
> (itinDateTime < Itinerary.itinDateTime
> OR
> (id <= Itinerary.id
> AND itinDateTime = Itinerary.itinDateTime)
> )
> );
> If you need more help, please post a CREATE TABLE statement (including
> constraints), some INSERT statements consisting of some sample data
> and then show what end result you want from that data.
> --
> David Portas, SQL Server MVP
> Whenever possible please post enough code to reproduce your problem.
> Including CREATE TABLE and INSERT statements usually helps.
> State what version of SQL Server you are using and specify the content
> of any error messages.
> SQL Server Books Online:
> http://msdn2.microsoft.com/library/ms130214(en-US,SQL.90).aspx
> --
>|||I must say that this appears to definitely be a case where the CTE produces
code that is much cleaner and easier to understand. I haven't felt that is
the case much (yet) since I haven't worked with CTEs prior to SQL 2005.
Perhaps they are starting to rub off on me!! :-)
--
TheSQLGuru
President
Indicium Resources, Inc.
"David Portas" <REMOVE_BEFORE_REPLYING_dportas@.acm.org> wrote in message
news:1181818331.032779.128580@.g37g2000prf.googlegroups.com...
> On 14 Jun, 11:31, Billy <B...@.discussions.microsoft.com> wrote:
>> Apologies, should have mentioned before am running on SQL 2000, sp4.
>> I am getting error:
>> "'ROW_NUMBER' is not a recognized function name"
>> Is ROW_NUMBER a 2005 only function?
>>
> ROW_NUMBER() was introduced in 2005.
> You didn't post any CREATE TABLE statement so I'll have to make a
> guess at what the key of your table is. I'll assume that the
> combination of (bkref, itinDateTime, ID) is unique:
> UPDATE Itinerary SET linenum => (SELECT COUNT(*)
> FROM Itinerary AS I
> WHERE bkref = Itinerary.bkref
> AND
> (itinDateTime < Itinerary.itinDateTime
> OR
> (id <= Itinerary.id
> AND itinDateTime = Itinerary.itinDateTime)
> )
> );
> If you need more help, please post a CREATE TABLE statement (including
> constraints), some INSERT statements consisting of some sample data
> and then show what end result you want from that data.
> --
> David Portas, SQL Server MVP
> Whenever possible please post enough code to reproduce your problem.
> Including CREATE TABLE and INSERT statements usually helps.
> State what version of SQL Server you are using and specify the content
> of any error messages.
> SQL Server Books Online:
> http://msdn2.microsoft.com/library/ms130214(en-US,SQL.90).aspx
> --
>