I've read (briefly) regarding enhanced ordering capabilities in this new
version of SQL server. For that matter, even in SQL Server 2005. I'm
currently using an SQL Server 2000 that contains a table of part information.
If the new enhanced ordering capabilities can achieve the desired result, I
would upgrade the server for that capability alone.

I would like some regarding ORDER BY clause. Maybe someone can
demonstrate the ordering enhancements in 2008... By utilizing this example:

First 2 chars is a MFG code, next 4 chars is a TYPE and the last 3 digits is
a SERIES number. Assume MFG and TYPE also exist in a column by themselves.
Using the following query:

SELECT PART FROM DATA ORDER BY MFG,TYPE,RIGHT(PART,3);

the data sample below is an abbreviated sample of data with the best
ordering I have been able to achieve using ORDER BY (in 2000).

PART
---------
AC1000142
AC1006120
AC1070118
AC1100133
AC1106124
AC1200102
AC1240109
AC1240111
AC1241109
AC1241111
AC1248106
AC1248119
AC1249106
AC1249119

Some of the part numbers are "singles" and some are "pairs".

What I would like to achieve is to have an ordered list with the "pairs" in
consequtive rows:

PART
------------
AC1000142
AC1006120
AC1070118
AC1100133
AC1106124
AC1200102
AC1240109 <-- pair
AC1241109
AC1240111 <-- pair
AC1241111
AC1248106 <-- pair
AC1249106
AC1248119 <-- pair
AC1249119

Can anyone demonstrate the new enhanced ORDER BY tricks to achieve this?

I don't know much about any new ORDER BY capabilities, but the query
below might interest you.

CREATE TABLE DATA (PART CHAR(10));
INSERT DATA
SELECT 'AC1000142' UNION ALL
SELECT 'AC1006120' UNION ALL
SELECT 'AC1070118' UNION ALL
SELECT 'AC1100133' UNION ALL
SELECT 'AC1106124' UNION ALL
SELECT 'AC1200102' UNION ALL
SELECT 'AC1240109' UNION ALL
SELECT 'AC1240111' UNION ALL
SELECT 'AC1241109' UNION ALL
SELECT 'AC1241111' UNION ALL
SELECT 'AC1248106' UNION ALL
SELECT 'AC1248119' UNION ALL
SELECT 'AC1249106' UNION ALL
SELECT 'AC1249119'

GO


SELECT A.PART
FROM DATA as A
LEFT OUTER
JOIN (SELECT RIGHT(PART,3) as Last3,
MIN(PART) as MinPart
FROM DATA
GROUP BY RIGHT(PART,3)
HAVING COUNT(*) > 1) as B
ON RIGHT(A.PART,3) = B.Last3
ORDER BY
COALESCE(MinPart, Part),
RIGHT(PART,3);

PART
----------
AC1000142
AC1006120
AC1070118
AC1100133
AC1106124
AC1200102
AC1240109
AC1241109
AC1241111
AC1240111
AC1248106
AC1249106
AC1249119
AC1248119

Roy Harvey
Beacon Falls, CT

On Sun, 14 Sep 2008 16:19:00 -0700, Mark H. Shin
<MarkHShin@discussions.microsoft.com> wrote:

>I've read (briefly) regarding enhanced ordering capabilities in this new
>version of SQL server. For that matter, even in SQL Server 2005. I'm
>currently using an SQL Server 2000 that contains a table of part information.
> If the new enhanced ordering capabilities can achieve the desired result, I
>would upgrade the server for that capability alone.
>
>I would like some regarding ORDER BY clause. Maybe someone can
>demonstrate the ordering enhancements in 2008... By utilizing this example:
>
>First 2 chars is a MFG code, next 4 chars is a TYPE and the last 3 digits is
>a SERIES number. Assume MFG and TYPE also exist in a column by themselves.
>Using the following query:
>
>SELECT PART FROM DATA ORDER BY MFG,TYPE,RIGHT(PART,3);
>
>the data sample below is an abbreviated sample of data with the best
>ordering I have been able to achieve using ORDER BY (in 2000).
>
>PART
>---------
>AC1000142
>AC1006120
>AC1070118
>AC1100133
>AC1106124
>AC1200102
>AC1240109
>AC1240111
>AC1241109
>AC1241111
>AC1248106
>AC1248119
>AC1249106
>AC1249119
>
>Some of the part numbers are "singles" and some are "pairs".
>
>What I would like to achieve is to have an ordered list with the "pairs" in
>consequtive rows:
>
>PART
>------------
>AC1000142
>AC1006120
>AC1070118
>AC1100133
>AC1106124
>AC1200102
>AC1240109 <-- pair
>AC1241109
>AC1240111 <-- pair
>AC1241111
>AC1248106 <-- pair
>AC1249106
>AC1248119 <-- pair
>AC1249119
>
>Can anyone demonstrate the new enhanced ORDER BY tricks to achieve this?

Very small error here: the Order By should be on « COALESCE(MinPart, Part),
Part » and not on
« COALESCE(MinPart, Part), RIGHT(Part,3) ».

--
Sylvain Lafontaine, ing.
MVP - Technologies Virtual-PC
E-mail: sylvain aei ca (fill the blanks, no spam please)


"Roy Harvey (SQL Server MVP)" <roy_harvey@snet.net> wrote in message
news:qbfrc4ddh1169vrgha43e2eb9fbfb3tq6q@4ax.com...
>I don't know much about any new ORDER BY capabilities, but the query
> below might interest you.
>
> CREATE TABLE DATA (PART CHAR(10));
> INSERT DATA
> SELECT 'AC1000142' UNION ALL
> SELECT 'AC1006120' UNION ALL
> SELECT 'AC1070118' UNION ALL
> SELECT 'AC1100133' UNION ALL
> SELECT 'AC1106124' UNION ALL
> SELECT 'AC1200102' UNION ALL
> SELECT 'AC1240109' UNION ALL
> SELECT 'AC1240111' UNION ALL
> SELECT 'AC1241109' UNION ALL
> SELECT 'AC1241111' UNION ALL
> SELECT 'AC1248106' UNION ALL
> SELECT 'AC1248119' UNION ALL
> SELECT 'AC1249106' UNION ALL
> SELECT 'AC1249119'
>
> GO
>
>
> SELECT A.PART
> FROM DATA as A
> LEFT OUTER
> JOIN (SELECT RIGHT(PART,3) as Last3,
> MIN(PART) as MinPart
> FROM DATA
> GROUP BY RIGHT(PART,3)
> HAVING COUNT(*) > 1) as B
> ON RIGHT(A.PART,3) = B.Last3
> ORDER BY
> COALESCE(MinPart, Part),
> RIGHT(PART,3);
>
> PART
> ----------
> AC1000142
> AC1006120
> AC1070118
> AC1100133
> AC1106124
> AC1200102
> AC1240109
> AC1241109
> AC1241111
> AC1240111
> AC1248106
> AC1249106
> AC1249119
> AC1248119
>
> Roy Harvey
> Beacon Falls, CT
>
> On Sun, 14 Sep 2008 16:19:00 -0700, Mark H. Shin
> <MarkHShin@discussions.microsoft.com> wrote:
>
>>I've read (briefly) regarding enhanced ordering capabilities in this new
>>version of SQL server. For that matter, even in SQL Server 2005. I'm
>>currently using an SQL Server 2000 that contains a table of part
>>information.
>> If the new enhanced ordering capabilities can achieve the desired result,
>> I
>>would upgrade the server for that capability alone.
>>
>>I would like some regarding ORDER BY clause. Maybe someone can
>>demonstrate the ordering enhancements in 2008... By utilizing this
>>example:
>>
>>First 2 chars is a MFG code, next 4 chars is a TYPE and the last 3 digits
>>is
>>a SERIES number. Assume MFG and TYPE also exist in a column by themselves.
>>Using the following query:
>>
>>SELECT PART FROM DATA ORDER BY MFG,TYPE,RIGHT(PART,3);
>>
>>the data sample below is an abbreviated sample of data with the best
>>ordering I have been able to achieve using ORDER BY (in 2000).
>>
>>PART
>>---------
>>AC1000142
>>AC1006120
>>AC1070118
>>AC1100133
>>AC1106124
>>AC1200102
>>AC1240109
>>AC1240111
>>AC1241109
>>AC1241111
>>AC1248106
>>AC1248119
>>AC1249106
>>AC1249119
>>
>>Some of the part numbers are "singles" and some are "pairs".
>>
>>What I would like to achieve is to have an ordered list with the "pairs"
>>in
>>consequtive rows:
>>
>>PART
>>------------
>>AC1000142
>>AC1006120
>>AC1070118
>>AC1100133
>>AC1106124
>>AC1200102
>>AC1240109 <-- pair
>>AC1241109
>>AC1240111 <-- pair
>>AC1241111
>>AC1248106 <-- pair
>>AC1249106
>>AC1248119 <-- pair
>>AC1249119
>>
>>Can anyone demonstrate the new enhanced ORDER BY tricks to achieve this?

You probably mean the new ranking functions in SQL Server 2005/2008.
They do provide very efficient and convenient mechanism for ranking,
which can be used for ordering result sets.

Here is one way to accomplish what you need using the ranking functions:

SELECT PART
FROM (
SELECT MFG, [TYPE], PART,
DENSE_RANK() OVER(PARTITION BY MFG
ORDER BY RIGHT(PART, 3)) AS rk1,
ROW_NUMBER() OVER(PARTITION BY MFG
ORDER BY [TYPE]) AS rk2
FROM DATA) AS T
ORDER BY MFG,
MIN([TYPE]) OVER(PARTITION BY MFG, rk1),
rk1,
rk2;

In the above DENSE_RANK and ROW_NUMBER are ranking functions. You can
read more about the syntax and use here:
http://msdn.microsoft.com/en-us/library/ms189798.aspx

Also, there is use of aggregate function with the OVER clause (MIN
OVER), you can read more about that here:
http://msdn.microsoft.com/en-us/library/ms189461.aspx

--
Plamen Ratchev
http://www.SQLStudio.com

On Sun, 14 Sep 2008 22:51:42 -0400, "Sylvain Lafontaine" <sylvain aei
ca (fill the blanks, no spam please)> wrote:

>Very small error here: the Order By should be on « COALESCE(MinPart, Part),
>Part » and not on
>« COALESCE(MinPart, Part), RIGHT(Part,3) ».

Yes, or course you are right. Thanks for that Sylvain.

Roy Harvey
Beacon Falls, CT