Is it better to Sort using a derived column or using a rank ?

  • 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

  • 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/

  • 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