Wrox Programmer Forums
Go Back   Wrox Programmer Forums > Database > SQL Language
| Search | Today's Posts | Mark Forums Read
SQL Language SQL Language discussions not specific to a particular RDBMS program or vendor.
Welcome to the p2p.wrox.com Forums.

You are currently viewing the SQL Language 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 April 12th, 2005, 12:14 AM
Registered User
 
Join Date: Apr 2005
Location: , , .
Posts: 4
Thanks: 0
Thanked 0 Times in 0 Posts
Default SQL "Order By" problem

I have an Access database with one table that contains a number of fields. I use the database with Visual Basic 6.0. One of the fields (CustNumber) contains a sequence that I need to show up in a specific sorted order, but a simple "Order By" doesn't work for me. The data in that field consists of one of two combinations. It will be either a "N" + a "number" OR a "N" + a "number" & a "B" + a "number"; such as N14 OR N3B12.

The first example is what I have, the second example is what I get after my Select statement and the third example is what I need to really end up with. Any help would be appreciated with the 'right' Order By.

My Select statement is:
Select * From TABLE Order By CustNumber

Example 1. Starting data ----->
CustNumber
  N1
  N21B7
  N4B1
  N31B81
  N4B5
  N4
  N21
  N31
  N31B6
  N50

Example 2. After the "Order By"
CustNumber
  N1
  N4
  N21
  N31
  N50
  N4B1
  N4B5
  N21B7
  N31B6
  N31B81

Example 3. What I really need!
CustNumber
  N1
  N4
  N4B1
  N4B5
  N21
  N21B7
  N31
  N31B6
  N31B81
  N50

 
Old April 12th, 2005, 04:14 PM
Friend of Wrox
 
Join Date: Jan 2004
Location: , , USA.
Posts: 303
Thanks: 0
Thanked 0 Times in 0 Posts
Default

may be you should create one more column and store your order (it could be a number like 1,2..etc.)in that column and simply use that column for order by..



 
Old April 13th, 2005, 03:57 AM
Friend of Wrox
 
Join Date: Jun 2003
Location: , , United Kingdom.
Posts: 1,212
Thanks: 0
Thanked 1 Time in 1 Post
Default

You could probably split up the customer number into 2 extra calculated fields in your query and then sort by those 2 fields. I'm not a big user of Access so not sure what string manipulation functions are available, but its going to be pretty messy.

If these numbers which are embedded in your customer number have some meaning on their own then you should've stored them separately in the first place, then you wouldn't have this mess. That's 1st normal form!
 
Old April 13th, 2005, 10:45 AM
Registered User
 
Join Date: Apr 2005
Location: , , .
Posts: 4
Thanks: 0
Thanked 0 Times in 0 Posts
Default

I have to stick with what I have because there is a lot of other code in my program that looks for this sequence. I just need a way to "sort" it in the database.





Similar Threads
Thread Thread Starter Forum Replies Last Post
Using ORDER BY in SQL on date field in ASP page saifi4u Classic ASP Professional 3 March 1st, 2008 07:40 PM
Using ORDER BY in SQL query in ASP page saifi4u Classic ASP Databases 1 February 29th, 2008 09:19 AM
SQL "Order By" problem bear88 SQL Language 1 April 12th, 2005 12:18 AM
Desc order problem dssachdeva Oracle 0 September 17th, 2003 04:42 AM
ASP SQL fields in order ALoPresto Classic ASP Databases 2 July 25th, 2003 02:54 PM





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