Weird query problem - 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)

Weird query problem

Author

Topic

MichaelP
Jedi Yak

2489 Posts

Posted - 2003-11-13 : 18:10:51

I've given a list of questions (think an online survey) and I need to be able to move back and forth between questions. We might have 1-5 questions per page, so I need to find the next / previous questions when the user clicks Next / Previous. This query below is trying to find the previous question. I'm on Question 3. I hit a "Previous" button and I should be at question 2. The code below returns Question 1. WHY!!!


DECLARE @Questions TABLE(QuestionID INT, Posistion INT)
DECLARE @LastQuestionID INT
DECLARE @QuestionsPerPage INT
DECLARE @NextQuestions TABLE(QuestionID INT)


SELECT @LastQuestionID = 3, @QuestionsPerPage = 1


INSERT INTO @Questions(QuestionID, Posistion) VALUES(1, 0)
INSERT INTO @Questions(QuestionID, Posistion) VALUES(2, 1)
INSERT INTO @Questions(QuestionID, Posistion) VALUES(3, 2)
INSERT INTO @Questions(QuestionID, Posistion) VALUES(4, 3)
INSERT INTO @Questions(QuestionID, Posistion) VALUES(5, 4)


--This select returns question 2, and then question 1 just like it should
SELECT q.QuestionID
FROM @Questions q
WHERE q.Posistion < (SELECT posistion FROM @Questions WHERE questionid = @LastQuestionID)
ORDER BY q.Posistion DESC


SET ROWCOUNT @QuestionsPerPage

--This inserts question 1 instead of question 2 into @NextQuestions
--WHY!@!@ 
INSERT INTO @NextQuestions(QuestionID)	
	SELECT q.QuestionID
	FROM @Questions q
	WHERE q.Posistion < (SELECT posistion FROM @Questions WHERE questionid = @LastQuestionID)
	ORDER BY q.Posistion DESC

SET ROWCOUNT 0


--This should have a single row with QuestionID 2 in it, not question 1
SELECT * FROM @NextQuestions

Any idea why it's doing this? Do I need a clustered index on my posistion field??

Michael

<Yoda>Use the Search page you must. Find the answer you will.</Yoda>

ehorn
Master Smack Fu Yak Hacker

1632 Posts

Posted - 2003-11-13 : 18:13:03

[code]
INSERT INTO @NextQuestions(QuestionID)
SELECT TOP 1 q.QuestionID
FROM @Questions q
WHERE q.Posistion < (SELECT posistion FROM @Questions WHERE questionid = @LastQuestionID)
ORDER BY q.Posistion DESC
[/code]

tkizer
Almighty SQL Goddess

38200 Posts

Posted - 2003-11-13 : 18:14:50

I'm getting 2 when I run it, just like you say it should.

Here are the result sets when I run your code without any modifications:



QuestionID  
----------- 
2
1

QuestionID  
----------- 
2

Tara

MichaelP
Jedi Yak

2489 Posts

Posted - 2003-11-13 : 18:15:35

Ehorn, I might not always want 1, that's sorta the point.

Lets say I've got 2 questions per page, and I'm on question 3. If I go back a page, I need to get back QuestionID's 1 and 2. You can't do a SELECT TOP @Variable, so thus the SET ROWCOUNT. But why does the INSERT grab data in a different order than how I ordered it in the ORDER BY??

Michael

<Yoda>Use the Search page you must. Find the answer you will.</Yoda>

ehorn
Master Smack Fu Yak Hacker

1632 Posts

Posted - 2003-11-13 : 18:16:27

That is wierd Tara...
When I ran it I got 1 as Michael said..

MichaelP
Jedi Yak

2489 Posts

Posted - 2003-11-13 : 18:17:49

Tara, you are? That's very strange. I wonder if there's some server setting that I have that's different than yours??

Any idea what might cause this?

Michael

<Yoda>Use the Search page you must. Find the answer you will.</Yoda>

tkizer
Almighty SQL Goddess

38200 Posts

Posted - 2003-11-13 : 18:18:45

What version are you running? I ran the query on both Personal Edition and Enterprise Edition. Both have the same results. Personal Edition is version 8.00.818, Enterprise Edition is 8.00.760.

Tara

MichaelP
Jedi Yak

2489 Posts

Posted - 2003-11-13 : 18:20:28

Microsoft SQL Server 2000 - 8.00.194 (Intel X86) Aug 6 2000 00:57:48 Copyright (c) 1988-2000 Microsoft Corporation Developer Edition on Windows NT 5.0 (Build 2195: Service Pack 2)

Maybe I need to install some SQL server service packs?

<Yoda>Use the Search page you must. Find the answer you will.</Yoda>

tkizer
Almighty SQL Goddess

38200 Posts

Posted - 2003-11-13 : 18:22:32

Yep install service pack 3a, which is what I have on both machines that I mentioned. I'm not sure why one is 760 and the other is 818. I haven't installed anything. The personal edition is my laptop while the other is a database server. Our IT department might have pushed a patch to my machine, but I'm not sure. Haven't heard of any patches for service pack 3a though.

Tara

ehorn
Master Smack Fu Yak Hacker

1632 Posts

Posted - 2003-11-13 : 18:23:07

quote:
Originally posted by MichaelP

Ehorn, I might not always want 1, that's sorta the point.

Lets say I've got 2 questions per page, and I'm on question 3. If I go back a page, I need to get back QuestionID's 1 and 2. You can't do a SELECT TOP @Variable, so thus the SET ROWCOUNT. But why does the INSERT grab data in a different order than how I ordered it in the ORDER BY??

I understand scratch top n

MichaelP
Jedi Yak

2489 Posts

Posted - 2003-11-13 : 18:29:56

Ok, I'm going to setup SQL server with sp3a on a test pc and see what happens. I don't want to try Sp3a on my box just yet :)

Thanks for all the help everyone!

Michael

<Yoda>Use the Search page you must. Find the answer you will.</Yoda>

ehorn
Master Smack Fu Yak Hacker

1632 Posts

Posted - 2003-11-13 : 18:45:43

Installing sp3a has resolved this behavior on my test box.
Good call Tara.

MichaelP
Jedi Yak

2489 Posts

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

Yep, SP3a on a test PC worked for me as well.

Can anyone explain why?

Michael

<Yoda>Use the Search page you must. Find the answer you will.</Yoda>

MichaelP
Jedi Yak

2489 Posts

Posted - 2003-11-13 : 20:04:36

sp_configure showed the same settings on both PC's.

Anything else I should look at, or did I just run into a bug that the service pack fixed?

Michael

<Yoda>Use the Search page you must. Find the answer you will.</Yoda>

tkizer
Almighty SQL Goddess

38200 Posts

Posted - 2003-11-13 : 20:06:10

It is probably just a bug. Would have to check the fix list to see what the bug was. That could be time consuming.

Tara

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources