order by clause, returning out of order at times

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)

order by clause, returning out of order at times

Author

Topic

mike123
Master Smack Fu Yak Hacker

1462 Posts

Posted - 2007-05-11 : 05:32:35

Hi,

I have a message forum that is intermittently returning the thread replies out of order. It's been working correctly for years under sql2000, and now that we have moved to sql2005 this problem comes and fixes itself at weird times.

We are ordering by DATE ASC, and the results sometimes a minute apart are brought back in a strange order.

Nothing has been changed whatsoever. I have absolutely 0 idea what this could be.

Has anyone heard or experienced such a thing?

Thanks!

spirit1
Cybernetic Yak Master

11752 Posts

Posted - 2007-05-11 : 05:36:25

can you show us the query?

_______________________________________________
Causing trouble since 1980
blog: http://weblogs.sqlteam.com/mladenp

madhivanan
Premature Yak Congratulator

22864 Posts

Posted - 2007-05-11 : 06:02:45

Also table structure

Madhivanan

Failing to plan is Planning to fail

tedmanowar
Starting Member

31 Posts

Posted - 2007-05-11 : 06:04:18

Show us the query, but are you converting the datetime value by any chance ? If you are, there's is a big chance that that's causing the problem.

TedManowar

X002548
Not Just a Number

15586 Posts

Posted - 2007-05-11 : 09:46:23

quote:
Originally posted by mike123

are brought back in a strange order.

What they said, and what does that mean?

Kinda hard to toubleshoot something so ambiguous

Brett

8-)

Hint: Want your questions answered fast? Follow the direction in this link
http://weblogs.sqlteam.com/brettk/archive/2005/05/25/5276.aspx

Add yourself!
http://www.frappr.com/sqlteam

jsmith8858
Dr. Cross Join

7423 Posts

Posted - 2007-05-11 : 10:20:08

10 dollars says someone at sqlTeam "helped" him by showing him how to format a DateTime in T-SQL using CONVERT() ....

- Jeff
http://weblogs.sqlteam.com/JeffS

spirit1
Cybernetic Yak Master

11752 Posts

Posted - 2007-05-11 : 10:31:48

i'm leaning more into the top 100 percent and order by getting short circuited.
if it's true that it worked on 200 and not on 2005

_______________________________________________
Causing trouble since 1980
blog: http://weblogs.sqlteam.com/mladenp

mike123
Master Smack Fu Yak Hacker

1462 Posts

Posted - 2007-05-14 : 05:11:58

Hey Guys,

Sorry for bein slow on a reply here. You guys do seem to be right on to the issue. Here is the query below causing the problems, as well as the table structure.

Any tips much appreciated!

Thanks again,
mike123



create PROCEDURE [dbo].[select_thread]
(
@topic_ID int, -- Topic ID
@Page smallint, -- Which Page
@RecsPerPage tinyint -- Number of Records to bring back
) 
AS SET NOCOUNT ON

Declare @TotalRows int

--Create temporary table to store records
CREATE TABLE #TempReply
(
 ID int IDENTITY,
userID int,
nameOnline varchar(25),
forum_Level tinyint,
forum_Posts int,
REPLY_ID int,
FORUM_ID tinyint,
R_AUTHOR int,
TOPIC_ID int,
R_STATUS tinyint,
R_DATE datetime,
R_MESSAGE text,
online tinyint null,
replyCount int null,
forum_Pic int
 )

INSERT INTO #TempReply (
	userID, nameOnline, forum_Level, forum_Posts, REPLY_ID, FORUM_ID, R_AUTHOR, TOPIC_ID, R_STATUS, R_DATE, R_MESSAGE, forum_Pic
) 
SELECT 
	tblUserDetails.userID, tblUserDetails.nameOnline, tblUserDetails.forum_Level, 
	tblUserDetails.forum_Posts, FORUM_REPLY.REPLY_ID, FORUM_REPLY.FORUM_ID, FORUM_REPLY.R_AUTHOR, FORUM_REPLY.TOPIC_ID, FORUM_REPLY.R_STATUS, FORUM_REPLY.R_DATE, FORUM_REPLY.R_MESSAGE,

tblUserDetails.forum_Pic  
 
FROM 
	tblUserDetails, FORUM_REPLY 

WHERE 
	tblUserDetails.userID = FORUM_REPLY.R_AUTHOR AND 
	TOPIC_ID = @topic_ID 
ORDER BY FORUM_REPLY.R_DATE asc


SELECT @TotalRows = @@Rowcount

-- find out the last and first record we want
DECLARE @FirstRec int, @LastRec int
SELECT @FirstRec = (@Page - 1) * @RecsPerPage 
SELECT @LastRec = (@Page * @RecsPerPage + 1)
--Now Return the set of paged records, plus indication of further records
SELECT ID, 
tr.userID, 
nameOnline, 
forum_Level, 
forum_Posts, 
REPLY_ID, 
FORUM_ID, 
R_AUTHOR, 
TOPIC_ID, 
R_STATUS, 
R_DATE, 
CASE WHEN tua.UserID IS NOT NULL THEN 1 ELSE 0 END as Online, 
@TotalRows as replyCount,
forum_Pic, 
MoreRecords = @TotalRows - @LastRec, 
R_MESSAGE 

FROM #TempReply tr
LEFT JOIN (
	SELECT DISTINCT UserID
	FROM tblActive_Users
) tua ON tua.userid = tr.userid

WHERE ID > @FirstRec AND ID < @LastRec 


SET NOCOUNT OFF





CREATE TABLE [dbo].[FORUM_REPLY](
	[CAT_ID] [tinyint] NOT NULL CONSTRAINT [FORUM_SnitzC63]  DEFAULT (0),
	[FORUM_ID] [tinyint] NOT NULL CONSTRAINT [FORUM_SnitzC64]  DEFAULT (0),
	[TOPIC_ID] [int] NOT NULL CONSTRAINT [FORUM_SnitzC65]  DEFAULT (0),
	[REPLY_ID] [int] IDENTITY(1,1) NOT NULL,
	[R_MAIL] [tinyint] NULL CONSTRAINT [FORUM_SnitzC66]  DEFAULT (0),
	[R_AUTHOR] [int] NULL CONSTRAINT [FORUM_SnitzC67]  DEFAULT (0),
	[R_MESSAGE] [text] NULL,
	[R_DATE] [datetime] NULL CONSTRAINT [FORUM_SnitzC68]  DEFAULT (''),
	[R_IP] [nvarchar](50) NULL CONSTRAINT [FORUM_SnitzC69]  DEFAULT ('000.000.000.000'),
	[R_STATUS] [tinyint] NULL CONSTRAINT [FORUM_SnitzC1017]  DEFAULT (0)
)

SwePeso
Patron Saint of Lost Yaks

30421 Posts

Posted - 2007-05-14 : 05:26:19

If you are using SQL Server 2005, please use ROW_NUMBER() or even NTILE() function to do pagination.

Peter Larsson
Helsingborg, Sweden

LoztInSpace
Aged Yak Warrior

940 Posts

Posted - 2007-05-14 : 05:33:06

The ORDER BY needs to be on the select, not the insert!

SwePeso
Patron Saint of Lost Yaks

30421 Posts

Posted - 2007-05-14 : 05:42:27

[code]ALTER PROCEDURE dbo.select_thread
(
@Topic_ID INT,
@Page SMALLINT,
@RecsPerPage TINYINT
)
AS

SET NOCOUNT ON

SELECT UserID,
NameOnline,
Forum_Level,
Forum_Posts,
REPLY_ID,
FORUM_ID,
R_AUTHOR,
TOPIC_ID,
R_STATUS,
R_DATE,
R_MESSAGE,
Forum_Pic
FROM (
SELECT u.UserID,
u.NameOnline,
u.Forum_Level,
u.Forum_Posts,
r.REPLY_ID,
r.FORUM_ID,
r.R_AUTHOR,
r.TOPIC_ID,
r.R_STATUS,
r.R_DATE,
r.R_MESSAGE,
u.Forum_Pic,
ROW_NUMBER() OVER (ORDER BY r.R_DATE) AS RecID
FROM tblUserDetails AS u
INNER JOIN FORUM_REPLY AS r ON r.R_AUTHOR = u.userID
WHERE r.TOPIC_ID = @Topic_ID
) AS d
WHERE RecID > (@Page - 1) * @RecsPerPage
AND RecID <= @Page * @RecsPerPage
ORDER BY R_DATE[/code]
Peter Larsson
Helsingborg, Sweden

SwePeso
Patron Saint of Lost Yaks

30421 Posts

Posted - 2007-05-14 : 05:43:45

quote:
Originally posted by LoztInSpace

The ORDER BY needs to be on the select, not the insert!

Both the insert (for creating a "line" number") and the final select.

Peter Larsson
Helsingborg, Sweden

mike123
Master Smack Fu Yak Hacker

1462 Posts

Posted - 2007-05-14 : 21:39:29

Hey Peso,

This query looks great and seems to be solving the ORDER BY problem.

Should we completely get rid of the temp table with this new approach? Should I see a performance increase with this method?

The only problem is its missing a few columns from original query and is causing a problem in the web app. I'm not sure the best way to add these missing columns or if the idea was to get rid of these.

The columns are

"ONLINE"
"REPLYCOUNT"
"MORERECORDS"

thanks very much once again :)
mike123

LoztInSpace
Aged Yak Warrior

940 Posts

Posted - 2007-05-15 : 01:33:24

For what it's worth, I don't think you ever need temp tables in SQL Server 2005 because of the row_number stuff. That is about the only reason I can think of for using them in 2000. Just work on your super gnarly SQL and avoid breaking it into bite size chunks for the hell of it.

mike123
Master Smack Fu Yak Hacker

1462 Posts

Posted - 2007-05-17 : 04:52:18

Hey Guys,

I'm been playin around with this for a couple days with no luck. I'm brand new to sql2005 and its new ROW_NUMBER() feature Peso suggested and I'm having a problem getting 2 values into my upgraded query. The values are

"REPLYCOUNT"
"MORERECORDS"

These records were set in my previous SPROC by going

SELECT @TotalRows = @@Rowcount
MoreRecords = @TotalRows - @LastRec

Since I don't have temp tables anymore, I'm not sure if or how I can set these values ?

Any help very much appreciated..

thanks again,
mike123

mike123
Master Smack Fu Yak Hacker

1462 Posts

Posted - 2007-05-17 : 20:00:49

bump for anyone that can help me, much appreciated been stuck on this all week =[

thanks again,
mike123

SwePeso
Patron Saint of Lost Yaks

30421 Posts

Posted - 2007-05-18 : 02:28:40

[code]ALTER PROCEDURE dbo.select_thread
(
@Topic_ID INT,
@Page SMALLINT,
@RecsPerPage TINYINT
)
AS

SET NOCOUNT ON

DECLARE @Records INT

SELECT @Records = COUNT(*)
FROM tblUserDetails AS u
INNER JOIN FORUM_REPLY AS r ON r.R_AUTHOR = u.userID

SELECT UserID,
NameOnline,
Forum_Level,
Forum_Posts,
REPLY_ID,
FORUM_ID,
R_AUTHOR,
TOPIC_ID,
R_STATUS,
R_DATE,
R_MESSAGE,
Forum_Pic
FROM (
SELECT u.UserID,
u.NameOnline,
u.Forum_Level,
u.Forum_Posts,
r.REPLY_ID,
r.FORUM_ID,
r.R_AUTHOR,
r.TOPIC_ID,
r.R_STATUS,
r.R_DATE,
r.R_MESSAGE,
u.Forum_Pic,
ROW_NUMBER() OVER (ORDER BY r.R_DATE) AS RecID
FROM tblUserDetails AS u
INNER JOIN FORUM_REPLY AS r ON r.R_AUTHOR = u.userID
WHERE r.TOPIC_ID = @Topic_ID
) AS d
WHERE RecID > (@Page - 1) * @RecsPerPage
AND RecID <= @Page * @RecsPerPage
ORDER BY R_DATE

SET @Records = @Records - @Page * @RecsPerPage[/code]
Peter Larsson
Helsingborg, Sweden

mike123
Master Smack Fu Yak Hacker

1462 Posts

Posted - 2007-05-18 : 05:05:46

Hi Peso,

Thanks very much, I owe you huge once again !! Very much appreciated..

(I was unsure on how to do the seperate SELECT, as I hadn't done before or it was a really long time ago... )

cheers,

mike123

Kristen
Test

22859 Posts

Posted - 2007-05-19 : 07:02:18

peso:

Do you reckon it is worthwhile doing a COUNT(*) over the record range, and then Paginating it?

(Rather than pulling the PKs into a Temp table, so that they are only iterated once - plus keeping the code for the WHERE clause in only one place, so less chance of new bugs during maintenance

)

Kristen

SwePeso
Patron Saint of Lost Yaks

30421 Posts

Posted - 2007-05-21 : 16:19:40

Hopefully the SLECT COUNT(*) is only run once, and then stored in a variable.

Peter Larsson
Helsingborg, Sweden

Kristen
Test

22859 Posts

Posted - 2007-05-22 : 01:52:41

I was meaning that the FROM, JOINs (and often some WHERE stuff) will appear twice - once for the COUNT(*) and again for the actual pagination).

When I need both I've always been inclined to grab the PKs into a Temp table, and do the COUNT(*) stuff and Pagination from the Temp table, but I've never done any empirical tests to see if that is more efficient.

Kristen

Next Page

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources