October 25, 2013 at 8:01 am
I am trying to sort some data with known duplicates.
In an effort to 'clean up' the sort, is it better to use the 'sort column + a unique ID' as a derived column, or create a rank?
Thanks
October 25, 2013 at 8:13 am
isuckatsql (10/25/2013)
I am trying to sort some data with known duplicates.In an effort to 'clean up' the sort, is it better to use the 'sort column + a unique ID' as a derived column, or create a rank?
Thanks
If you already have a unique ID you would probably be best going that path. If you are using RANK there is a bit of overhead to calculate the value. This is my best guess based on the limited amount of information posted.
_______________________________________________________________
Need help? Help us help you.
Read the article at http://www.sqlservercentral.com/articles/Best+Practices/61537/ for best practices on asking questions.
Need to split a string? Try Jeff Modens splitter http://www.sqlservercentral.com/articles/Tally+Table/72993/.
Cross Tabs and Pivots, Part 1 – Converting Rows to Columns - http://www.sqlservercentral.com/articles/T-SQL/63681/
Cross Tabs and Pivots, Part 2 - Dynamic Cross Tabs - http://www.sqlservercentral.com/articles/Crosstab/65048/
Understanding and Using APPLY (Part 1) - http://www.sqlservercentral.com/articles/APPLY/69953/
Understanding and Using APPLY (Part 2) - http://www.sqlservercentral.com/articles/APPLY/69954/
October 25, 2013 at 8:34 am
set statistics time on;
DECLARE @StartOfRange VARCHAR(MAX)
DECLARE @EndOfRange VARCHAR(MAX)
DECLARE @s-2 INT = '1';
DECLARE @e INT = '9';
DECLARE @OrderType VARCHAR(10) = 'asc';
DECLARE @OrderBy VARCHAR(50) = 'postalcode + cast(e.id as varchar(50))';
--DECLARE @OrderBy NVARCHAR(300) = 'title + cast(e.id as nvarchar(300))';
--DECLARE @OrderBy VARCHAR(50) = 'lastmodified + e.id';
DECLARE @sql NVARCHAR(MAX) = N';
WITH results AS
(
SELECT e.id, title, LastModified, postalcode, RowNum = ROW_NUMBER() OVER (ORDER BY ' + @OrderBy + ' ' + @OrderType + ')
FROM dbo.EmploymentOpportunities e
inner join companys c on e.companyid=c.id
--where CompanyId = 148
)
SELECT id, title, LastModified, postalcode, (select count(*) from results) totalcount
FROM results
WHERE RowNum BETWEEN @s-2 AND @e
ORDER BY RowNum
OPTION(Maxdop 8)
;';
EXEC sp_executesql @sql, N'@s INT, @e INT', 1, 9;
--EXEC sp_executesql @sql, N'1, 9', @StartOfRange, @EndOfRange;
Thanks for the feedback !
Since you mentioned that Rank might be labor intensive, i used the derived column and received the results below.
In the above code sorting by title+e.id works fast and accurate, sorting by postalcode+e.id works slow(due to the join), but accurate, and sorting by Lastmodified+e.id works fast but is inaccurate.
I am not sure why Lastmodified+e.id is not working correctly?
Viewing 3 posts - 1 through 2 (of 2 total)
You must be logged in to reply to this topic. Login to reply