Sorting

Thread view:

Enable EMail Alerts

Start New Thread

Thread rating:

Audrey Ng - 31 Jul 2008 15:18 GMT

Hello everyone,

I have a sorting issue.

I have a bunch of records, let's call them NOTES.

Field NOTEID (which is data type INT in the database, but was converted
to nvarchar in the stored procedure) is being sorted alphabetically
(i.e. 1,10,100,2,3,4,5,6,7,8,9)
and need to be in order. The easy fix would have been to sort it
numerically.

However, for each of these NOTEID, you can have a child NOTEID that
needs to stay together. If NOTEID sorted numerically, it would look
something like this:
(i.e. 1,2,3,4,5,6,7,8,9,10,10-11,10-12,13,14,10-15)

The 10 is the parent NOTE ID
10-11,10-12,10-15 are PARENTNOTEID + NOTEID

This is incorrect because the child records (10-15) must be after 10-12
.

so, I need a solution to achieve the following:

1,2,3,4,5,6,7,8,9,10,10-11,10-12,10-15,13,14

Reply to this Message

Eric Russell - 31 Jul 2008 17:08 GMT

Even if you want to return a calculated nvarchar column that combines
PARENTNOTEID with NOTEID, then you can still specify PARENTNOTEID and NOTEID
in the ORDER BY clause.

For example:
select cast(PARENTNOTEID as nvarchar(9)) + '-' + cast(NOTEID as nvarchar(9))
from MyTable
order by PARENTNOTEID, NOTEID

> Hello everyone,
>
[quoted text clipped - 24 lines]
>
> *** Sent via Developersdex http://www.developersdex.com ***

Reply to this Message

Plamen Ratchev - 31 Jul 2008 17:23 GMT

You should be able to order directly:

SELECT <columns>
FROM Foo
ORDER BY parentnoteid, noteid;

If the columns are already concatenated, you can still split for the
ordering purpose:

SELECT x
FROM (SELECT '1' UNION
SELECT '2' UNION
SELECT '3' UNION
SELECT '4' UNION
SELECT '5' UNION
SELECT '6' UNION
SELECT '7' UNION
SELECT '8' UNION
SELECT '9' UNION
SELECT '10' UNION
SELECT '10-11' UNION
SELECT '10-12' UNION
SELECT '13' UNION
SELECT '14' UNION
SELECT '10-15') AS T(x)
ORDER BY CASE WHEN CHARINDEX('-', x) > 0
THEN CAST(LEFT(x, CHARINDEX('-', x) - 1) AS INT)
ELSE CAST(x AS INT)
END,
CASE WHEN CHARINDEX('-', x) > 0
THEN CAST(RIGHT(x, LEN(x) -
CHARINDEX('-', x)) AS INT)
ELSE 0
END;

Plamen Ratchev
http://www.SQLStudio.com

Reply to this Message