Sort a Numbering System with Dashes in It
Moderator: General Moderators
Sort a Numbering System with Dashes in It
My company has documents with a numbering system as follows: 1-1, 1-2, 2-3, 5-3, 10-2, 2-101, etc... Numbers and dashes. I am storing info about these documents, such as these numbers in a mysql database and then outputting the info into an HTML table on the web page. I was wanting to be able to sort the table ascending and descending based on this numbering system. Does anyone have any ideas how to do this? I tried converting the dashes to decimals but that fails whenever you have more than two numbers after the dash. Any help would be appreciated.
-
alex.barylski
- DevNet Evangelist
- Posts: 6267
- Joined: Tue Dec 21, 2004 5:00 pm
- Location: Winnipeg
Re: Sort a Numbering System with Dashes in It
MySQL should sort those documents without problem...
Maybe your query is incorrect. POST the SQL query and your table schema.
Maybe your query is incorrect. POST the SQL query and your table schema.
-
WebbieDave
- Forum Contributor
- Posts: 213
- Joined: Sun Jul 15, 2007 7:07 am
Re: Sort a Numbering System with Dashes in It
Right. If, for some reason unbeknownst to us, you need to sort these elements without benefit of SQL (ORDER BY), you can use usort.Hockey wrote:MySQL should sort those documents without problem
http://us.php.net/manual/en/function.usort.php
Re: Sort a Numbering System with Dashes in It
try the following (not tested):
Code: Select all
ORDER BY CONVERT(INT, SUBSTRING_INDEX(nr_colomn, '-', 1)), CONVERT(INT, SUBSTRING_INDEX(nr_colomn, '-', -1))Re: Sort a Numbering System with Dashes in It
I would like it work using SQL if possible. The name of my field that needs to be sorted(with the dashes in it) is named Exhibit Number. It is currently a varchar type. I tried this sql command based on the last post. I'm not sure I did the command correctly.
$results=$exhibits->query("select * from exhibits ORDER BY CONVERT(INT, SUBSTRING_INDEX('`Exhibit Number`', '-', 1)), CONVERT(INT, SUBSTRING_INDEX('`Exhibit Number`', '-', -1))");
$results=$exhibits->query("select * from exhibits ORDER BY CONVERT(INT, SUBSTRING_INDEX('`Exhibit Number`', '-', 1)), CONVERT(INT, SUBSTRING_INDEX('`Exhibit Number`', '-', -1))");
Re: Sort a Numbering System with Dashes in It
Wouldn't alphabetical sorting on that column do something like this?Hockey wrote:MySQL should sort those documents without problem...
1-1
1-10
1-2
1-3
.... etc
In that case, that's probably not what you would want, right?
Re: Sort a Numbering System with Dashes in It
I think I just tried a regular ORDER BY and that's what it did. That is not what I'm looking for.
- Christopher
- Site Administrator
- Posts: 13596
- Joined: Wed Aug 25, 2004 7:54 pm
- Location: New York, NY, US
Re: Sort a Numbering System with Dashes in It
Easiest would be to either separate the two numbers into two columns in the table, or zero pad them internally "01-07". Or do something like:
ORDER BY CAST(LEFT(mycode, LOCATE('-', mycode)-1) AS UNSIGNED), CAST(SUBSTRING(mycode, LOCATE('-', mycode)+1) AS UNSIGNED)
ORDER BY CAST(LEFT(mycode, LOCATE('-', mycode)-1) AS UNSIGNED), CAST(SUBSTRING(mycode, LOCATE('-', mycode)+1) AS UNSIGNED)
(#10850)
Re: Sort a Numbering System with Dashes in It
I think this problem may be close to impossible to resolve in SQL alone. Your biggest hurdle is the fact that you have to split the document ID by the dashes and then add zero padding to the document number, otherwise documents 1-12 and 11-2 would both wind up as "112".
In PHP, your best bet may be to retrieve all the records from the database, split the document ID string and add zero padding, then do the actual sort.
My advice would be to standardize in a document ID format that is "sortable" for the future; that is, require zero padding when entering document IDs.
In PHP, your best bet may be to retrieve all the records from the database, split the document ID string and add zero padding, then do the actual sort.
My advice would be to standardize in a document ID format that is "sortable" for the future; that is, require zero padding when entering document IDs.
Re: Sort a Numbering System with Dashes in It
Would this work with documents that might have 3 digits after the dash, such as 1-202? Also, if I was somehow able split the document IDs and and add the zeros, how would I sort the whole table based on the document ID field? Would I use a php sort method? Or use SQL ORDER BY on the split document ID fields?
- Christopher
- Site Administrator
- Posts: 13596
- Joined: Wed Aug 25, 2004 7:54 pm
- Location: New York, NY, US
Re: Sort a Numbering System with Dashes in It
You really need to start trying things. Both manixrock and I have given you SQL. They do essentially the same thing.
(#10850)
Re: Sort a Numbering System with Dashes in It
kinggabbo wrote:I would like it work using SQL if possible. The name of my field that needs to be sorted(with the dashes in it) is named Exhibit Number. It is currently a varchar type. I tried this sql command based on the last post. I'm not sure I did the command correctly.
$results=$exhibits->query("select * from exhibits ORDER BY CONVERT(INT, SUBSTRING_INDEX('`Exhibit Number`', '-', 1)), CONVERT(INT, SUBSTRING_INDEX('`Exhibit Number`', '-', -1))");
You need to pass as the first parameter to SUBSTRING_INDEX the name of the column (without any dashes or quotes) holding the dash-separated numbers. For example, if the column name is "product_ids", then your query should be:
Code: Select all
ORDER BY CONVERT(INT, SUBSTRING_INDEX(product_ids, '-', 1)), CONVERT(INT, SUBSTRING_INDEX(product_ids, '-', -1))Re: Sort a Numbering System with Dashes in It
I found help on another forum that was similar to what some had posted here. I still need to test it some, but I think it works. Here is the SQL:
SELECT * FROM table ORDER BY
CAST(LEFT(`Exhibit Number`, LOCATE('-', `Exhibit Number`)-1) AS SIGNED INTEGER),
CAST(RIGHT(`Exhibit Number`, LENGTH(`Exhibit Number`) - LOCATE('-', `Exhibit Number`)) AS SIGNED INTEGER)
Thanks for all the help!
SELECT * FROM table ORDER BY
CAST(LEFT(`Exhibit Number`, LOCATE('-', `Exhibit Number`)-1) AS SIGNED INTEGER),
CAST(RIGHT(`Exhibit Number`, LENGTH(`Exhibit Number`) - LOCATE('-', `Exhibit Number`)) AS SIGNED INTEGER)
Thanks for all the help!
Re: Sort a Numbering System with Dashes in It
Works like a charm indeed
When I said "I think this problem may be close to impossible to resolve in SQL alone." I did not realize that you only ever have one dash in your document ID, I misunderstood and thought your problem was that you could have an arbitrarily long doc ID like 1-23-1234-123-45...
Glad you found the answer, and thanks for sharing it!
When I said "I think this problem may be close to impossible to resolve in SQL alone." I did not realize that you only ever have one dash in your document ID, I misunderstood and thought your problem was that you could have an arbitrarily long doc ID like 1-23-1234-123-45...
Glad you found the answer, and thanks for sharing it!