December 15, 2009 at 7:55 am
I'm having an issue with a cursor and ORDER BY clause. I have the following cursor that inserts data into a database.
DECLARE machine_cursor CURSOR FOR
SELECT machineID, machineType FROM machinema A
LEFT JOIN modelma B ON B.startmachine = A.machineID
WHERE A.modelID = @modelID
ORDER BY startmachine DESC
OPEN machine_cursor
FETCH NEXT FROM machine_cursor
INTO @machineID, @machineType
WHILE @@FETCH_STATUS = 0
BEGIN
SET @newmachineID = NEWID()
INSERT INTO XMLmodelMap(sessionID, oldmodelID, newmodelID, oldmachineID, newmachineID, machineType) VALUES
(@mySessionID, @modelID, @newmodelID, @machineID, @newmachineID, @machineType)
FETCH NEXT FROM machine_cursor
INTO @machineID, @machineType
END
CLOSE machine_cursor
DEALLOCATE machine_cursor
The problem is, the table XMLmodelMap, is not in the same order as the query above.
Does ORDER BY not work with cursors?
Thanks!
December 15, 2009 at 8:01 am
What is the table definition for XMLmodelMap and what indexes are defined?
Also, the only way to ensure order in a query is to include an ORDER BY clause in the query. Just because you insert the data into the table in a specific order is no guaranttee that the data is stored in that order or will be returned in that order when queried.
December 15, 2009 at 8:12 am
Lynn's post says it all but in case if it makes easier for you, question would be
'how do you know that records are not in the order?'
---------------------------------------------------------------------------------
December 15, 2009 at 8:29 am
Well if I do this query:
SELECT machineID, machineType FROM machinema A
LEFT JOIN modelma B ON B.startmachine = A.machineID
WHERE A.modelID = @modelID
ORDER BY startmachine DESC
it is in the order I need.
But when I query the table, XMLmodelMap, after it's populated by the cursor, it is not in the same order.
So I guess the cursor doesn't use my order by clause?
Thanks!
December 15, 2009 at 8:51 am
I think the issue is located in the definition of table XML....
Please post the definition of the table.
Other point, for such an easy task you do not need a cursor.
December 15, 2009 at 8:57 am
Here is my table
/****** Object: Table [dbo].[XMLmodelMap] Script Date: 12/15/2009 10:55:21 ******/
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
CREATE TABLE [dbo].[XMLmodelMap](
[sessionID] [nvarchar](128) NOT NULL,
[oldModelID] [uniqueidentifier] NOT NULL,
[newModelID] [uniqueidentifier] NOT NULL,
[oldMachineID] [nvarchar](50) NOT NULL,
[newMachineID] [nvarchar](50) NOT NULL,
[machineType] [tinyint] NULL
) ON [PRIMARY]
Thanks!
December 15, 2009 at 9:29 am
Magy (12/15/2009)
Well if I do this query:
SELECT machineID, machineType FROM machinema A
LEFT JOIN modelma B ON B.startmachine = A.machineID
WHERE A.modelID = @modelID
ORDER BY startmachine DESC
it is in the order I need.
But when I query the table, XMLmodelMap, after it's populated by the cursor, it is not in the same order.
So I guess the cursor doesn't use my order by clause?
Thanks!
I am confident that the cursor ordered the data as you asked. As I said, though, that is no guaranttee that your data will be stored in that order, nor returned in that order with a simple SELECT statement if you do not include an ORDER BY clause. To ensure the order of data in a query you MUST use an ORDER BY clause.
December 15, 2009 at 12:07 pm
But it didn't order the data according to my ORDER BY clause...that's why I'm confused.
When I run the cursor, and then check the table, it is not in the order as the original query.
Thanks!
December 15, 2009 at 12:34 pm
Magy (12/15/2009)
But it didn't order the data according to my ORDER BY clause...that's why I'm confused.When I run the cursor, and then check the table, it is not in the order as the original query.
Thanks!
How do you know? After you ran your script with the cursor, did you then do this:
select * from dbo.XMLmodelMap
Without an ORDER BY clause on the SELECT there is NO GUARANTTEE in the order of the data. Your cursor was declared with an ORDER BY, therefore it processed that data in the order you wanted. There is NO GUARANTTEE, however, that the data is stored in the table dbo.XMLmodelMap in that order.
December 15, 2009 at 6:10 pm
Yeah, I did that...and the order was not the same.
I was under the impression that it would be...
Thanks!
December 16, 2009 at 1:13 am
If you need an order without using SELECT * FROM XML... without an ORDER BY clause than you can add a primarykey to your table like Id INT IDENTITY and
ALTER TABLE XML... ADD CONSTRAINT PK_??? PRIMARY KEY CLUSTERED (Id).
This will garantee that the output is in that order you put the records in.
December 16, 2009 at 7:36 am
Thomas Lampe (12/16/2009)
... This will garantee that the output is in that order you put the records in.
First, it isn't that it is a primary key but that it is a clustered index. And I would be careful with this as well as you may still not get the data as expected. Again, to ensure order of a result set, you need to include the ORDER BY clause in the SELECT.
December 16, 2009 at 12:13 pm
Using an order by clause on the newly populated table will not work because there is nothing unique about the data. That's why I was hoping the cursor would populate the table based on the order by
December 16, 2009 at 12:28 pm
Magy (12/16/2009)
Using an order by clause on the newly populated table will not work because there is nothing unique about the data. That's why I was hoping the cursor would populate the table based on the order by
Care to explain why using an order by on a newly populated table wouldn't work? There is nothing in the above statment that makes sense to me.
December 17, 2009 at 9:56 am
Hey,
I noticed you're using a cursor! isn't that forbidden? that a cursor is bad news for performance!
If I had the same task I'd use something like the following solution (wrote it free hand, didnt parse it):
DECLARE @MachineMA Table (Ident INT IDENTITY(1,1), MachineID INT, MachineType INT)
DECLARE @RecordCount INT, @RowNumber INT
INSERT INTO @MachineMA (MachineID, MachineType)
SELECT machineID, machineType FROM machinema A
LEFT JOIN modelma B ON B.startmachine = A.machineID
WHERE A.modelID = @modelID
ORDER BY startmachine DESC
SELECT @RowNumber = 1, @RecordCount = COUNT(Ident) FROM @MachineMA
WHILE @RowNumber <= @RecordCount
BEGIN
SET @newmachineID = NEWID()
SELECT @machineID = MachineID, @machineType = MachineType FROM @MachineMA WHERE Ident = @RowNumber
INSERT INTO XMLmodelMap(sessionID, oldmodelID, newmodelID, oldmachineID, newmachineID, machineType) VALUES
(@mySessionID, @modelID, @newmodelID, @machineID, @newmachineID, @machineType)
SET @RowNumber = @RowNumber + 1
END
Viewing 15 posts - 1 through 15 (of 24 total)
You must be logged in to reply to this topic. Login to reply