Order By Help - 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 2008 Forums

Analysis Server and Reporting Services (2008)

Order By Help

Author

Topic

ZMike
Posting Yak Master

110 Posts

Posted - 2012-07-05 : 16:08:29

I have a ROW_NUMBER() option in my SSRS Report

Example

SELECT *
,ROW_NUMBER() OVER(ORDER BY Zip5 , Street) AS RowNumber
FROM Customers

ORDER BY Zip5 , Street

However the actual query that's running is ignoring my order by in my main dataset. This is messing up my Row Number.

I did turn the sort options on in both the group and the tablix and it will sort correctly but it's leaving my RowNumber all messed up since it's not presorting.

The main reason I was doing this is 2 reasons.

1. So I could let users get a certain amount of data (like using a top 10)

2. So that I can also use the row numbers in my header

Text Box 1 (Right Aligned)
= "Records " & MIN(ReportItems!RowNumber.Value) & " to "

TextBox2 (Left Aligned)
=MAX(ReportItems!RowNumber.Value) & " of "
& Count(Fields!RowNumber.Value, "DataSet1") & " Total Records"

If I could make the Main Order By Clause work before the dataset has to sort it then I'd be golden.

visakh16
Very Important crosS Applying yaK Herder

52326 Posts

Posted - 2012-07-05 : 16:15:51

what do you mean by its ignoring order by? your select should give you records in order of Zip5,Street. what else are you expecting to get as output?

------------------------------------------------------------------------------------------------------
SQL Server MVP
http://visakhm.blogspot.com/

ZMike
Posting Yak Master

110 Posts

Posted - 2012-07-05 : 16:27:45

visakh16,

Hopefully This makes sense

My Dataset 1 consists of

SELECT *
,ROW_NUMBER() OVER(ORDER BY Zip5 , Street) AS RowNumber
FROM Customers
ORDER BY Zip5 , Street

** Note , I have all fields listed instead of Select * but due to the data I cannot post it on here. **

However what the DataSet1 Appears to be doing is this

SELECT *
FROM Customers

If I run this query in Managment Studio and then go and look at my RowNumber Column it looks to just be doing a select * and giving the order that it's coming back (Without an OrderBy)

I've read that this can be an issue sometimes and you will have to go to :
Row Groups
Group Properties
Sorting

and then set the sorting here. Which is fine. I can make the data sort that direction. But since it's happening after it's already ran the data, my ROW_NUMBER() OVER(ORDER BY Zip5 , Street) data is no longer in order.

So instead of showing

Address 1 RowNUmber 1
Address 2 RowNumber 2
Address 3 RowNumber 3

It's Showing

Address 1 RowNumber 3
Address 2 RowNumber 1
Address 3 RowNumber 2

So it is showing the main data in the correct order, but only after I either go into RowGroups or the tablix and set the sorting there.

visakh16
Very Important crosS Applying yaK Herder

52326 Posts

Posted - 2012-07-05 : 16:57:35

are there multiple records with same address? anyways this should give you result on order of row number


SELECT *
FROM
(
SELECT *
,ROW_NUMBER() OVER(ORDER BY Zip5 , Street) AS RowNumber
FROM Customers
)t
ORDER BY RowNumber

------------------------------------------------------------------------------------------------------
SQL Server MVP
http://visakhm.blogspot.com/

ZMike
Posting Yak Master

110 Posts

Posted - 2012-07-05 : 22:38:47

Visakh16,

If I run the query in the query designer then I get data as follows which is correct...

State Zip5 Street Address RowNumber

Washington 99201 Main Street 123 1
Washington 99201 Main Street 234 2
Washington 99201 Main Street 345 3
Washington 99201 New Rd 1 4

State Zip5 Street Address RowNumber

Washington 99201 Main Street 123 3
Washington 99201 Main Street 234 2
Washington 99201 Main Street 345 1
Washington 99201 New Rd 1 4

So it will list them correctly. But again only if I go into the tablix and put the sorting there. It seems to ignore my order by in the dataset itself. If I try to add the Row Number in the Tablix Sorting then it would be like this

State Zip5 Street Address RowNumber

Washington 99201 Main Street 345 1
Washington 99201 Main Street 123 2
Washington 99201 New Rd 1 3
Washington 99201 Main Street 234 4

visakh16
Very Important crosS Applying yaK Herder

52326 Posts

Posted - 2012-07-05 : 22:44:40

you cant guarantee order of display inside tablix unless you put an explicit sorting in it

------------------------------------------------------------------------------------------------------
SQL Server MVP
http://visakhm.blogspot.com/

ZMike
Posting Yak Master

110 Posts

Posted - 2012-07-05 : 23:29:49

Visakh16,

I'm not sure if this is the best way but it's working.

IF OBJECT_ID('tempdb..#Customers') IS NOT NULL
DROP TABLE #Customers

SELECT *

INTO #Customers
FROM Customers

ORDER BY Zip5 , Street

SELECT * , ROW_NUMBER() OVER(ORDER BY Zip5 , Street) AS RowNumber
FROM #Customers

This is keeping the correct order by since I put it in the temp table first.

visakh16
Very Important crosS Applying yaK Herder

52326 Posts

Posted - 2012-07-05 : 23:41:14

thats fine but unless you put an explicit sort on tablix you cant guarantee the order of retrieval.

------------------------------------------------------------------------------------------------------
SQL Server MVP
http://visakhm.blogspot.com/

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources