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
  #1 (permalink)  
Old September 2nd, 2005, 03:33 AM
Registered User
 
Join Date: Sep 2005
Location: Craiova, , Romania.
Posts: 5
Thanks: 0
Thanked 0 Times in 0 Posts
Default Ordering rows problem

Hy everybody.

I hope someone could give me some help.

I have the following rows in the database (varchar type):
tableColumn
-------------------
12text
1text
123text


I have to get the rows sorted like this:
1text
12text
123text

I just need a query...or any ideas? I use stored procedures......


HELP!!!!!!!!


Floriana P
  #2 (permalink)  
Old September 2nd, 2005, 03:40 AM
Registered User
 
Join Date: Sep 2005
Location: Craiova, , Romania.
Posts: 5
Thanks: 0
Thanked 0 Times in 0 Posts
Default

The example is not exactly what I had in mind:

Here is a new one:

1atext
12text
12atext
1text


I need the rows ordered like that:

1atext
12atext
1text
12text

. The result I get is:
1text
1atext
12text
12atext


HELP!!!







Floriana P
  #3 (permalink)  
Old September 4th, 2005, 11:34 PM
Friend of Wrox
Points: 4,332, Level: 27
Points: 4,332, Level: 27 Points: 4,332, Level: 27 Points: 4,332, Level: 27
Activity: 0%
Activity: 0% Activity: 0% Activity: 0%
 
Join Date: Nov 2003
Location: , NJ, USA.
Posts: 1,348
Thanks: 0
Thanked 5 Times in 5 Posts
Default

That is how the rows will be ordered if ordering on that column. To change the order you need to order on a different column. I would suggest an identity column.

  #4 (permalink)  
Old September 5th, 2005, 07:23 AM
Friend of Wrox
 
Join Date: Jun 2003
Location: Hudson, MA, USA.
Posts: 839
Thanks: 0
Thanked 1 Time in 1 Post
Default

Quote:
quote:
I would suggest an identity column.
And just how would ordering on an identity column solve the OP's problem? What relationship would the value of the identity column on a given row have to the column to be sorted so that the the rows would be "properly" ordered? What if a new row were inserted with a sort column value in between existing rows. The identity value would be "far away" from the existing rows, so how would that help?

To the OP:

The problem is that the column is a composite column - a value comprised of two other values. This is a violation of first normal form and this design is likely to be doomed to ongoing difficulties. The fact that you want to sort in a particular way on two distinct portions of the column tells me you have attached meaning to each distinct piece. Why did you combine these meanings into a single value?

Suppose instead that the original column was split into two columns: the first containing the numeric portion of the original column, and the second containing the string portion. Then, if I understand your desired ordering, it would be by the string portion and then the numeric portion within each string value.

Separate them out; things will be much easier.

If you really can't (why?), you could (if SQL Server 2000), write two user defined functions: one to parse off and return the leading numeric portion, and another the string portion. You would then order by the string function, then the numeric function value within the string value.

In SQL Server 2000, you can use the SUBSTRING and ISNUMERIC functions to extract off the leading numeric characters of the input column then CAST them to a numeric value.

Using functions this way would be very slow. It would be orders of magnitude faster if the column values were split correctly in the first place...

Good luck.

Jeff Mason
Custom Apps, Inc.
www.custom-apps.com
  #5 (permalink)  
Old September 5th, 2005, 09:03 AM
Registered User
 
Join Date: Sep 2005
Location: Craiova, , Romania.
Posts: 5
Thanks: 0
Thanked 0 Times in 0 Posts
Default

I receive the value of the column from my client (there are product codes). I import the products in my database and then in some form he wants to see those codes - but ordered the way I write in the first topic.

I tried using regex and user defined functions - in some way the same you suggested, but the order is not exactly the order my client wants (3text goes before 31text but after 3)......


Thanks.


Amalia

Floriana P
  #6 (permalink)  
Old September 5th, 2005, 09:17 AM
Friend of Wrox
 
Join Date: Jun 2003
Location: Hudson, MA, USA.
Posts: 839
Thanks: 0
Thanked 1 Time in 1 Post
Default

Can you tell me the rule which describes the ordering, rather than just giving examples?

It looks like you want the ordering by the text portion of the column, then by the numeric portion of the column, within a given text value. Note that an empty string is a perfectly good text value, so it would sort first. Is the numeric portion to be in numeric order or string order? That is, does 3 come before or after 10?

Jeff Mason
Custom Apps, Inc.
www.custom-apps.com
  #7 (permalink)  
Old September 15th, 2005, 06:08 AM
Registered User
 
Join Date: Sep 2005
Location: Craiova, , Romania.
Posts: 5
Thanks: 0
Thanked 0 Times in 0 Posts
Default

I want to sort:
- the text
- the numbers (that can miss of some row values) - but not in their value order, but in their ...ASCII order I guess....1text goes in front of 11text.The problem is that the values of my rows can be: "11text-22text texttext".

Any way, I started parsing every value of every row - but it is runs slow (and on some values not very exact either).

Floriana P
  #8 (permalink)  
Old September 15th, 2005, 06:25 AM
Friend of Wrox
 
Join Date: Jun 2003
Location: Hudson, MA, USA.
Posts: 839
Thanks: 0
Thanked 1 Time in 1 Post
Default

Well, as I said in an earlier response,
Quote:
quote:
The problem is that the column is a composite column - a value comprised of two other values. This is a violation of first normal form and this design is likely to be doomed to ongoing difficulties.
You need to find a way to "fix" this, or we'll be having conversations like this from now one...

Since you said you were importing the data which is encoded this way into your database, you may want to consider splitting this composite column into two columns in your database table during the import. As each row is imported, parse off any leading numeric characters and store them into one column, defined as a character data type. The remaining text (once you encounter a nonnumeric character) goes into a second column.

Then you can do any query on this table and order by the second column (the text) then the first (the numeric portion, but in string order).

This way, you slow the import down (which you only do once in a while), but your queries run faster. (And my normalization sensibilities are no longer offended ;).)

Jeff Mason
Custom Apps, Inc.
www.custom-apps.com
  #9 (permalink)  
Old October 7th, 2005, 10:20 PM
Friend of Wrox
 
Join Date: Aug 2004
Location: Orange County, CA, USA.
Posts: 385
Thanks: 0
Thanked 0 Times in 0 Posts
Default

I suggest using other fields and breaking up the data. Use logic to pull out the number in the beginning into one filed, and the character values to another. Then you can vary your sort without altering the data you were sent in this field. If thats what you want I need to know how many numberic values you can have before getting into the alpha characters.

  #10 (permalink)  
Old October 7th, 2005, 10:21 PM
Friend of Wrox
 
Join Date: Aug 2004
Location: Orange County, CA, USA.
Posts: 385
Thanks: 0
Thanked 0 Times in 0 Posts
Default

another suggestion is to not accept cr$p data and force your source to send data in a more reasonable way.





Similar Threads
Thread Thread Starter Forum Replies Last Post
Ordering price when multi-currency table jwhite68 SQL Server 2000 3 June 13th, 2007 08:46 AM
Ordering a dataview Louisa VB.NET 2002/2003 Basics 1 November 11th, 2004 10:04 AM
Ordering results EstherMStrom XSLT 6 October 13th, 2004 09:50 AM
alternate ordering polecat XSLT 17 October 2nd, 2003 08:35 AM
Ordering lists POL XSLT 3 June 9th, 2003 11:39 PM





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