Wrox Programmer Forums
|
SQL Server 2000 General discussion of Microsoft SQL Server -- for topics that don't fit in one of the more specific SQL Server forums. version 2000 only. There's a new forum for SQL Server 2005.
Welcome to the p2p.wrox.com Forums.

You are currently viewing the SQL Server 2000 section of the Wrox Programmer to Programmer discussions. This is a community of software programmers and website developers including Wrox book authors and readers. New member registration was closed in 2019. New posts were shut off and the site was archived into this static format as of October 1, 2020. If you require technical support for a Wrox book please contact http://hub.wiley.com
 
Old August 19th, 2008, 12:43 PM
Friend of Wrox
 
Join Date: Jul 2003
Posts: 112
Thanks: 0
Thanked 0 Times in 0 Posts
Default ORDER BY Help

I would like some help regarding ORDER BY clause for a SQL Server 2000 database.

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...

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

Some the number 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

Does anyone know of any ORDER BY tricks to achieve this?
 
Old August 19th, 2008, 01:57 PM
Friend of Wrox
 
Join Date: Jun 2008
Posts: 1,649
Thanks: 3
Thanked 141 Times in 140 Posts
Default

Well, the real problem is that you "fibbed" to us a bit.

You said "First 2 chars is a MFG code, next 4 chars is a TYPE and the last 3 digits is a SERIES number"

But given the way you *WANT* to order those records, that's simply not true.

In point of fact, what you *really* have is:

First 2 characters are a MFG code
next 3 chars is a TYPE code
next ONE CHARACTER is a SUB-TYPE code that should be IGNORED
the last 3 digits is a SERIES number

Your problem occurs because indeed you try to use a 4 character TYPE code, and it really isn't true.

But in any case, we can simplify this code even more:

First FIVE characters are a prefix
Next ONE character is a SUB-TYPE code that should be ignored for ordering purposes
Last THREE characters are the suffix

So:

SELECT part FROM data
ORDER BY LEFT(part,5), RIGHT(part,3)

Presto.

Now, it's possible that this still isn't quite the right answer, but it certainly works for the sample data that you presented.
 
Old August 19th, 2008, 02:46 PM
Friend of Wrox
 
Join Date: Jul 2003
Posts: 112
Thanks: 0
Thanked 0 Times in 0 Posts
Default

I certainly appreciate your input. But, SELECT part FROM data
ORDER BY LEFT(part,5), RIGHT(part,3) results in random ordering:

e.g. AC1006120 sometimes comes before AC1000142...

I've been playing around with LEFT(),SUBSTRING() and RIGHT() functions but w/o success. I looked online and there were some SQL Server 2005 specific examples using RANK(), but SQL 2000 does not support it.

What I really have is two different orderings depending on the TYPE

Single TYPE: ORDER BY MFG,TYPE,SERIES;
Paired TYPE: ORDER BY MFG,SERIES,TYPE;

Overall Order should still maintain the ordering of TYPE...


 
Old August 19th, 2008, 03:19 PM
Friend of Wrox
 
Join Date: Jun 2008
Posts: 1,649
Thanks: 3
Thanked 141 Times in 140 Posts
Default

Well, I don't see any simple solution, given the sample data you provided and the desired output you asked for.

YOu have conflicting requirements.

Your definition of "pair" means that you *MUST* ignore the last digit of the type code. Clearly, if you do not, then the ordering would have to be

AC1240109
AC1240111
AC1241109
AC1241111

So the fact that you want

AC1000142
AC1006120

to be in that order conflicts with the need to make the pairs shown here:

AC1240109 <-- pair
AC1241109
AC1240111 <-- pair
AC1241111

So you can't do this via a simple "order by".

I think you'll need to make a much, much more complex query to create the output exactly as you want it. Let me think on it...
 
Old August 19th, 2008, 03:55 PM
Friend of Wrox
 
Join Date: Jul 2003
Posts: 112
Thanks: 0
Thanked 0 Times in 0 Posts
Default

You are correct, of course.

My next step was going to be to try using SHAPE to return one dimensional list of non-paired and left-side paired (even TYPE) with sub recordset for the right-side pair (odd TYPE associated pair e.g. 1240/1241) which I'm pretty sure will work, but it makes the SQL and code to handle SHAPE recordsets much more complicated.

 
Old August 19th, 2008, 04:24 PM
Friend of Wrox
 
Join Date: Jun 2008
Posts: 1,649
Thanks: 3
Thanked 141 Times in 140 Posts
Default

Okay, it's not as ugly as I thought...

Looking at your data again:

AC1000142
AC1006120
AC1240109 <-- pair
AC1241109
AC1240111 <-- pair
AC1241111

First, consider this query:

Code:
SELECT LEFT(part,5) AS prefix, MIN(SUBSTRING(part,6,1) AS subtype,  RIGHT(part,3) AS series
FROM data
GROUP BY LEFT(part,5) + RIGHT(part,3)
I think you can see that will give you output such as

prefix, subtype, series
AC100, 0, 142
AC100, 6, 120
AC124, 0, 109
AC124, 0, 111

So now you can do:

Code:
SELECT data.part
FROM data,
     ( SELECT LEFT(part,5) AS prefix, MIN(SUBSTRING(part,6,1) AS subtype,  RIGHT(part,3) AS series
       FROM data
       GROUP BY LEFT(part,5) + RIGHT(part,3) ) AS check
WHERE LEFT(data.part,5) = check.prefix AND RIGHT(data.part,3) = check.series
ORDER BY check.prefix, check.subtype, check.series, data.part
See why it should work???

After doing the JOIN of the two tables, the joined data looks like this:
prefix, subtype, series, part
AC100, 0, 142, AC1000142
AC100, 6, 120, AC1006120
AC124, 0, 109, AC1240109
AC124, 0, 109, AC1241109
AC124, 0, 111, AC1240111
AC124, 0, 111, AC1241111

And in that order, of course!
 
Old August 19th, 2008, 08:18 PM
Friend of Wrox
 
Join Date: Oct 2006
Posts: 475
Thanks: 0
Thanked 9 Times in 9 Posts
Default

<s>If you take a real close look at the data being used, just a simple ORDER BY on the column will work just fine... no need to split a thing... and, yes, it will achieve the pairing naturally.</s>

Sorry folks... I need a new seeing-eye dog. :D

--Jeff Moden
 
Old August 19th, 2008, 08:49 PM
Friend of Wrox
 
Join Date: Jun 2008
Posts: 1,649
Thanks: 3
Thanked 141 Times in 140 Posts
Default

Ummm... Jeff, can I recommend a good optometrist??

Looking at his examples:

AC1240109
AC1241109
AC1240111
AC1241111
AC1248106
AC1249106
AC1248119
AC1249119

If you just do an ORDER BY those values, then the last time *I* checked, I would get

AC1240109
AC1240111
AC1241109
AC1241111
AC1248106
AC1248119
AC1249106
AC1249119

Which sure doesn't look very well "paired" to me.
 
Old August 19th, 2008, 10:40 PM
Friend of Wrox
 
Join Date: Oct 2006
Posts: 475
Thanks: 0
Thanked 9 Times in 9 Posts
Default

Heh... you're right... I need a seeing-eye dog or something. Wow... more coffee please! ;)

--Jeff Moden
 
Old August 20th, 2008, 09:45 AM
Friend of Wrox
 
Join Date: Jul 2003
Posts: 112
Thanks: 0
Thanked 0 Times in 0 Posts
Default

Excellent! Thank you for your input/help!

I did make a slight addition to the GROUP BY clause of the sub query to get it to work properly in Management Studio and Query Analyzer:

,LEFT(part,5),RIGHT(part,3)

The errors were:

Column 'DATA.PART' is invalid in the select list because it is not contained in either an aggregate function or the GROUP BY clause.





Similar Threads
Thread Thread Starter Forum Replies Last Post
Order by In Report mateenmohd Access 3 December 6th, 2006 08:17 AM
ORDER BY a column? gilgalbiblewheel Classic ASP Databases 1 March 30th, 2005 10:37 AM
Order By with Union agarwalmk SQL Language 3 February 18th, 2004 11:33 AM
Order by on a subform bjackman Access 1 January 16th, 2004 01:22 PM
document node order vs sort node order. ladyslipper98201 XSLT 2 June 5th, 2003 11:06 AM





Powered by vBulletin®
Copyright ©2000 - 2020, Jelsoft Enterprises Ltd.
Copyright (c) 2020 John Wiley & Sons, Inc.