Wrox Programmer Forums

Need to download code?

View our list of code downloads.

Go Back   Wrox Programmer Forums > SQL Server > SQL Server 2000 > SQL Server 2000
Password Reminder
Register
| FAQ | Members List | Calendar | Search | Today's Posts | Mark Forums Read
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 tens of thousands of software programmers and website developers including Wrox book authors and readers. As a guest, you can read any forum posting. By joining today you can post your own programming questions, respond to other developers’ questions, and eliminate the ads that are displayed to guests. Registration is fast, simple and absolutely free .
DRM-free e-books 300x50
Reply
 
Thread Tools Search this Thread Display Modes
  #1 (permalink)  
Old August 19th, 2008, 12:43 PM
Friend of Wrox
Points: 422, Level: 7
Points: 422, Level: 7 Points: 422, Level: 7 Points: 422, Level: 7
Activity: 0%
Activity: 0% Activity: 0% Activity: 0%
 
Join Date: Jul 2003
Location: La Jolla, CA, USA.
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?
Reply With Quote
  #2 (permalink)  
Old August 19th, 2008, 01:57 PM
Friend of Wrox
 
Join Date: Jun 2008
Location: Snohomish, WA, USA
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.
Reply With Quote
  #3 (permalink)  
Old August 19th, 2008, 02:46 PM
Friend of Wrox
Points: 422, Level: 7
Points: 422, Level: 7 Points: 422, Level: 7 Points: 422, Level: 7
Activity: 0%
Activity: 0% Activity: 0% Activity: 0%
 
Join Date: Jul 2003
Location: La Jolla, CA, USA.
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...


Reply With Quote
  #4 (permalink)  
Old August 19th, 2008, 03:19 PM
Friend of Wrox
 
Join Date: Jun 2008
Location: Snohomish, WA, USA
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...
Reply With Quote
  #5 (permalink)  
Old August 19th, 2008, 03:55 PM
Friend of Wrox
Points: 422, Level: 7
Points: 422, Level: 7 Points: 422, Level: 7 Points: 422, Level: 7
Activity: 0%
Activity: 0% Activity: 0% Activity: 0%
 
Join Date: Jul 2003
Location: La Jolla, CA, USA.
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.

Reply With Quote
  #6 (permalink)  
Old August 19th, 2008, 04:24 PM
Friend of Wrox
 
Join Date: Jun 2008
Location: Snohomish, WA, USA
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!
Reply With Quote
  #7 (permalink)  
Old August 19th, 2008, 08:18 PM
Friend of Wrox
Points: 1,536, Level: 15
Points: 1,536, Level: 15 Points: 1,536, Level: 15 Points: 1,536, Level: 15
Activity: 0%
Activity: 0% Activity: 0% Activity: 0%
 
Join Date: Oct 2006
Location: , MI, USA.
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
Reply With Quote
  #8 (permalink)  
Old August 19th, 2008, 08:49 PM
Friend of Wrox
 
Join Date: Jun 2008
Location: Snohomish, WA, USA
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.
Reply With Quote
  #9 (permalink)  
Old August 19th, 2008, 10:40 PM
Friend of Wrox
Points: 1,536, Level: 15
Points: 1,536, Level: 15 Points: 1,536, Level: 15 Points: 1,536, Level: 15
Activity: 0%
Activity: 0% Activity: 0% Activity: 0%
 
Join Date: Oct 2006
Location: , MI, USA.
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
Reply With Quote
  #10 (permalink)  
Old August 20th, 2008, 09:45 AM
Friend of Wrox
Points: 422, Level: 7
Points: 422, Level: 7 Points: 422, Level: 7 Points: 422, Level: 7
Activity: 0%
Activity: 0% Activity: 0% Activity: 0%
 
Join Date: Jul 2003
Location: La Jolla, CA, USA.
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.
Reply With Quote
Reply


Thread Tools Search this Thread
Search this Thread:

Advanced Search
Display Modes

Posting Rules
You may not post new threads
You may not post replies
You may not post attachments
You may not edit your posts

BB code is On
Smilies are On
[IMG] code is Off
HTML code is Off
Trackbacks are Off
Pingbacks are On
Refbacks are Off


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



All times are GMT -4. The time now is 02:20 AM.


Powered by vBulletin®
Copyright ©2000 - 2019, Jelsoft Enterprises Ltd.
© 2013 John Wiley & Sons, Inc.