October 16, 2009 at 1:45 pm
The code below is a sample paging code with two ORDER BY statements. I'm confused - which ORDER BY takes precedence??
;WITH cols
AS
(
SELECT table_name, column_name,
ROW_NUMBER() OVER(ORDER BY table_name, column_name) AS seq
FROM [INFORMATION_SCHEMA].columns
)
SELECT table_name, column_name
FROM cols
WHERE seq BETWEEN @startRow AND @startRow + 49
ORDER BY seq
tx,
Matt
October 16, 2009 at 1:49 pm
matt6749 (10/16/2009)
The code below is a sample paging code with two ORDER BY statements. I'm confused - which ORDER BY takes precedence??;WITH cols
AS
(
SELECT table_name, column_name,
ROW_NUMBER() OVER(ORDER BY table_name, column_name) AS seq
FROM [INFORMATION_SCHEMA].columns
)
SELECT table_name, column_name
FROM cols
WHERE seq BETWEEN @startRow AND @startRow + 49
ORDER BY seq
tx,
Matt
Hi Matt,
The first order by you see in the query, is only used to generate an incremental ID that is used for the time of the query. This ID is then aliased as "seq" and used in the select's order by. You often can see that operator (ROW_Number() over(Partition by A order by B)) used with a CTE (the "WITH" keyword), and is really useful to generate an incremental ID.
Hope that helps,
Cheers,
J-F
October 16, 2009 at 1:57 pm
Thank you - that helps. For some reason the CASE statement below, for sorting, is not resulting in the specified sort order.. any ideas? tx Matt
SELECT * FROM
(
SELECT
CASE
WHEN @sortColumn = 'ProviderID' THEN ROW_NUMBER()OVER (ORDER BY P.ProviderID)
WHEN @sortColumn = 'NumViews DESC' THEN ROW_NUMBER()OVER (ORDER BY P.NumViews DESC)
WHEN @sortColumn = 'AvgRating DESC' THEN ROW_NUMBER()OVER (ORDER BY P.AvgRating DESC)
WHEN @sortColumn = 'LastName' THEN ROW_NUMBER()OVER (ORDER BY P.LastName)
ELSE
ROW_NUMBER()OVER (ORDER BY P.ProviderID ASC)
END
AS RowNum,
P.ProviderID, P.OccupationID, P.LastName, P.FirstName, P.Designations, P.NumRatings, P.AvgRating, P.City, P.Zip, P.IsRated, P.NumViews, P.ApprovedStatus, O.OccupationName, PPA.PracticeAreaID
FROM dbo.Providers P WITH (NOLOCK)
INNER JOIN dbo.Occupations O ON O.OccupationID = P.OccupationID
INNER JOIN dbo.ProvidersPracticeAreas PPA ON PPA.ProviderID = P.ProviderID
GROUP BY P.ProviderID, P.OccupationID, P.LastName, P.FirstName, P.Designations, P.NumRatings, P.AvgRating, P.City, P.Zip, P.IsRated, P.NumViews, P.ApprovedStatus, O.OccupationName, PPA.PracticeAreaID
HAVING PPA.PracticeAreaID = @practiceAreaID
AND (@occupationID IS NULL OR P.OccupationID = @occupationID)
AND (@zip IS NULL OR P.Zip = @zip)
AND (@lastName IS NULL OR P.LastName LIKE @lastName + '%')
AND (@approvedStatus = @approvedStatus)
)
AS XYZ -- you need this AS XYZ
WHERE RowNum BETWEEN @startRow AND (@startRow + @rowsPerPage) - 1
October 16, 2009 at 2:12 pm
Well, you have no order by clause.. all you do is number your rows in the case statement, and you don't add the order by seq asc, at the bottom of your query.
As I said in the explanation, the order by in the row_number is only used to number the rows in a specified order, it will not ensure your data is sorted. You need to sort by this column in the bottom order by clause if you want your data sorted.
Cheers,
J-F
October 16, 2009 at 2:19 pm
Now I understand!!! Thanks a lot J-F!!!
Matt, NYC
October 16, 2009 at 2:21 pm
My pleasure Matt,
Have a nice day!
Cheers,
J-F
Viewing 6 posts - 1 through 5 (of 5 total)
You must be logged in to reply to this topic. Login to reply