MySQL Help
 
Forums: » Register « |  User CP |  Games |  Calendar |  Members |  FAQs |  Sitemap |  Support | 
User Name:
Password:
Remember me

The Shed is going Social! Join us on FaceBook and Twitter and chime in on the conversation.

Go Back   Dev Shed Forums > Databases > MySQL Help
Reload this Page

PHP-General - Ordering Numbers


Reply
Add This Thread To:
  Del.icio.us   Digg   Google   Spurl   Blink   Furl   Simpy   Y! MyWeb 
« Previous Thread | Next Thread »  
Thread Tools Search this Thread Rate Thread Display Modes
 
Unread Dev Shed Forums Sponsor:
  #1  
Old March 19th, 2013, 08:33 AM
Sarath_PHP Sarath_PHP is offline
Registered User
Dev Shed Newbie (0 - 499 posts)
  
Join Date: Feb 2013
Posts: 20 Sarath_PHP User rank is Just a Lowly Private (1 - 20 Reputation Level) 
Time spent in forums: 6 h 11 m 46 sec
Reputation Power: 0
PHP-General - Ordering Numbers
Hi,

I used mysql query "SELECT DISTINCT productname FROM tablename GROUP BY productname" for getting products from table.Result is

"A4 Heavy Cover Booklets 12pp
A4 Heavy Cover Booklets 16pp
A4 Heavy Cover Booklets 20pp
A4 Heavy Cover Booklets 24pp
A4 Heavy Cover Booklets 28pp
A4 Heavy Cover Booklets 80pp
A4 Heavy Cover Booklets 8pp "


But, How can we get results starting with "A4 Heavy Cover Booklets 8pp"?

Example:
A4 Heavy Cover Booklets 8pp
A4 Heavy Cover Booklets 12pp
A4 Heavy Cover Booklets 16pp
A4 Heavy Cover Booklets 20pp
A4 Heavy Cover Booklets 24pp
A4 Heavy Cover Booklets 28pp
A4 Heavy Cover Booklets 80pp

Please help me.


Thanks
Reply With Quote
  #2  
Old March 19th, 2013, 08:38 AM
ishnid's Avatar
ishnid ishnid is offline
kill 9, $$;
Dev Shed God 4th Plane (6500 - 6999 posts)
  
Join Date: Sep 2001
Location: Shanghai, An tSín
Posts: 6,894 ishnid User rank is General 44th Grade (Above 100000 Reputation Level)ishnid User rank is General 44th Grade (Above 100000 Reputation Level)ishnid User rank is General 44th Grade (Above 100000 Reputation Level)ishnid User rank is General 44th Grade (Above 100000 Reputation Level)ishnid User rank is General 44th Grade (Above 100000 Reputation Level)ishnid User rank is General 44th Grade (Above 100000 Reputation Level)ishnid User rank is General 44th Grade (Above 100000 Reputation Level)ishnid User rank is General 44th Grade (Above 100000 Reputation Level)ishnid User rank is General 44th Grade (Above 100000 Reputation Level)ishnid User rank is General 44th Grade (Above 100000 Reputation Level)ishnid User rank is General 44th Grade (Above 100000 Reputation Level)ishnid User rank is General 44th Grade (Above 100000 Reputation Level)ishnid User rank is General 44th Grade (Above 100000 Reputation Level)ishnid User rank is General 44th Grade (Above 100000 Reputation Level)ishnid User rank is General 44th Grade (Above 100000 Reputation Level)ishnid User rank is General 44th Grade (Above 100000 Reputation Level) 
Time spent in forums: 4 Months 2 Weeks 1 Day 22 h 14 m 53 sec
Reputation Power: 3885
This is an SQL question rather than a PHP one so I've moved it to the appropriate forum.
Last edited by ishnid : March 19th, 2013 at 08:43 AM.
Reply With Quote
  #3  
Old March 19th, 2013, 08:41 AM
dariyoosh's Avatar
dariyoosh dariyoosh is offline
Contributing User
Dev Shed Newbie (0 - 499 posts)
  
Join Date: Nov 2012
Location: Iran / France
Posts: 132 dariyoosh User rank is First Lieutenant (10000 - 20000 Reputation Level)dariyoosh User rank is First Lieutenant (10000 - 20000 Reputation Level)dariyoosh User rank is First Lieutenant (10000 - 20000 Reputation Level)dariyoosh User rank is First Lieutenant (10000 - 20000 Reputation Level)dariyoosh User rank is First Lieutenant (10000 - 20000 Reputation Level)dariyoosh User rank is First Lieutenant (10000 - 20000 Reputation Level)dariyoosh User rank is First Lieutenant (10000 - 20000 Reputation Level)dariyoosh User rank is First Lieutenant (10000 - 20000 Reputation Level) 
Time spent in forums: 2 Days 6 h 13 m 3 sec
Reputation Power: 133
Why not adding an ORDER BY clause directly to your query?

Here is an example based on your sample data:
Code:
mysql> SELECT productName
    -> FROM
    -> (
    ->     SELECT 'A4 Heavy Cover Booklets 12pp' AS productName FROM DUAL UNION ALL
    ->     SELECT 'A4 Heavy Cover Booklets 16pp' AS productName FROM DUAL UNION ALL
    ->     SELECT 'A4 Heavy Cover Booklets 20pp' AS productName FROM DUAL UNION ALL
    ->     SELECT 'A4 Heavy Cover Booklets 24pp' AS productName FROM DUAL UNION ALL
    ->     SELECT 'A4 Heavy Cover Booklets 28pp' AS productName FROM DUAL UNION ALL
    ->     SELECT 'A4 Heavy Cover Booklets 80pp' AS productName FROM DUAL UNION ALL
    ->     SELECT 'A4 Heavy Cover Booklets 8pp' AS productName FROM DUAL
    -> ) AS products_tab
    -> GROUP BY productName
    -> ORDER BY productName DESC;
+------------------------------+
| productName                  |
+------------------------------+
| A4 Heavy Cover Booklets 8pp  |
| A4 Heavy Cover Booklets 80pp |
| A4 Heavy Cover Booklets 28pp |
| A4 Heavy Cover Booklets 24pp |
| A4 Heavy Cover Booklets 20pp |
| A4 Heavy Cover Booklets 16pp |
| A4 Heavy Cover Booklets 12pp |
+------------------------------+
7 rows in set (0.00 sec)

mysql>



Regards,
Dariyoosh
Reply With Quote
  #4  
Old March 19th, 2013, 08:51 AM
Sarath_PHP Sarath_PHP is offline
Registered User
Dev Shed Newbie (0 - 499 posts)
  
Join Date: Feb 2013
Posts: 20 Sarath_PHP User rank is Just a Lowly Private (1 - 20 Reputation Level) 
Time spent in forums: 6 h 11 m 46 sec
Reputation Power: 0
Hi dariyoosh,

Thanks for you reply.Actually my client need
"A4 Heavy Cover Booklets 8pp
A4 Heavy Cover Booklets 12pp
A4 Heavy Cover Booklets 16pp
A4 Heavy Cover Booklets 20pp
A4 Heavy Cover Booklets 24pp
A4 Heavy Cover Booklets 28pp
A4 Heavy Cover Booklets 80pp"
this result.
Reply With Quote
  #5  
Old March 19th, 2013, 08:53 AM
Sarath_PHP Sarath_PHP is offline
Registered User
Dev Shed Newbie (0 - 499 posts)
  
Join Date: Feb 2013
Posts: 20 Sarath_PHP User rank is Just a Lowly Private (1 - 20 Reputation Level) 
Time spent in forums: 6 h 11 m 46 sec
Reputation Power: 0
Hi ishnid,

This is not an SQL base question.It may be PHP.
Reply With Quote
  #6  
Old March 19th, 2013, 09:46 AM
Jacques1's Avatar
Jacques1 Jacques1 is offline
pollyanna
Click here for more information.
  
Join Date: Jul 2012
Location: Germany
Posts: 1,835 Jacques1 User rank is Lieutenant General (80000 - 90000 Reputation Level)Jacques1 User rank is Lieutenant General (80000 - 90000 Reputation Level)Jacques1 User rank is Lieutenant General (80000 - 90000 Reputation Level)Jacques1 User rank is Lieutenant General (80000 - 90000 Reputation Level)Jacques1 User rank is Lieutenant General (80000 - 90000 Reputation Level)Jacques1 User rank is Lieutenant General (80000 - 90000 Reputation Level)Jacques1 User rank is Lieutenant General (80000 - 90000 Reputation Level)Jacques1 User rank is Lieutenant General (80000 - 90000 Reputation Level)Jacques1 User rank is Lieutenant General (80000 - 90000 Reputation Level)Jacques1 User rank is Lieutenant General (80000 - 90000 Reputation Level)Jacques1 User rank is Lieutenant General (80000 - 90000 Reputation Level)Jacques1 User rank is Lieutenant General (80000 - 90000 Reputation Level)Jacques1 User rank is Lieutenant General (80000 - 90000 Reputation Level)Jacques1 User rank is Lieutenant General (80000 - 90000 Reputation Level)Jacques1 User rank is Lieutenant General (80000 - 90000 Reputation Level) 
Time spent in forums: 1 Month 2 Weeks 1 Day 4 h 14 m 14 sec
Reputation Power: 811
Hi,

your actual problem is that your table structure is broken. You have different data all crammed into one big string. This means that the data is basically inaccessible to the database system -- which brings up the question why you even use a relational database system.

Sure, you can do some workarounds with PHP. But that's just ugly, slow, and you'll fight the same problems again and again (as you can see in your previous posts).

If you have any chance to fix the structure, do it. Look up "normalization", specifically "first normal form". That's the only real solution. Otherwise, well, prepare for an endless fight and many, many ugly workarounds that will kill the performance. You'll have to put all rows into an array and then call usort on it. The custom compare function has to extract each number with a regex and then sort by that.
Reply With Quote
  #7  
Old March 19th, 2013, 11:09 AM
r937's Avatar
r937 r937 is offline
SQL Consultant
Click here for more information.
  
Join Date: Feb 2003
Location: Toronto Canada
Posts: 26,357 r937 User rank is General 47th Grade (Above 100000 Reputation Level)r937 User rank is General 47th Grade (Above 100000 Reputation Level)r937 User rank is General 47th Grade (Above 100000 Reputation Level)r937 User rank is General 47th Grade (Above 100000 Reputation Level)r937 User rank is General 47th Grade (Above 100000 Reputation Level)r937 User rank is General 47th Grade (Above 100000 Reputation Level)r937 User rank is General 47th Grade (Above 100000 Reputation Level)r937 User rank is General 47th Grade (Above 100000 Reputation Level)r937 User rank is General 47th Grade (Above 100000 Reputation Level)r937 User rank is General 47th Grade (Above 100000 Reputation Level)r937 User rank is General 47th Grade (Above 100000 Reputation Level)r937 User rank is General 47th Grade (Above 100000 Reputation Level)r937 User rank is General 47th Grade (Above 100000 Reputation Level)r937 User rank is General 47th Grade (Above 100000 Reputation Level)r937 User rank is General 47th Grade (Above 100000 Reputation Level)r937 User rank is General 47th Grade (Above 100000 Reputation Level) 
Time spent in forums: 3 Months 1 Week 2 Days 4 h 33 m 45 sec
Reputation Power: 4140
Quote:
Originally Posted by dariyoosh
Code:
    -> ORDER BY productName DESC;

| A4 Heavy Cover Booklets 8pp  |
| A4 Heavy Cover Booklets 80pp |
because ORDER BY isn't good enough

you said DESC, so 8pp should've come last instead of first
__________________
r937.com | rudy.ca
please visit Simply SQL and buy my book
Reply With Quote
  #8  
Old March 19th, 2013, 11:10 AM
r937's Avatar
r937 r937 is offline
SQL Consultant
Click here for more information.
  
Join Date: Feb 2003
Location: Toronto Canada
Posts: 26,357 r937 User rank is General 47th Grade (Above 100000 Reputation Level)r937 User rank is General 47th Grade (Above 100000 Reputation Level)r937 User rank is General 47th Grade (Above 100000 Reputation Level)r937 User rank is General 47th Grade (Above 100000 Reputation Level)r937 User rank is General 47th Grade (Above 100000 Reputation Level)r937 User rank is General 47th Grade (Above 100000 Reputation Level)r937 User rank is General 47th Grade (Above 100000 Reputation Level)r937 User rank is General 47th Grade (Above 100000 Reputation Level)r937 User rank is General 47th Grade (Above 100000 Reputation Level)r937 User rank is General 47th Grade (Above 100000 Reputation Level)r937 User rank is General 47th Grade (Above 100000 Reputation Level)r937 User rank is General 47th Grade (Above 100000 Reputation Level)r937 User rank is General 47th Grade (Above 100000 Reputation Level)r937 User rank is General 47th Grade (Above 100000 Reputation Level)r937 User rank is General 47th Grade (Above 100000 Reputation Level)r937 User rank is General 47th Grade (Above 100000 Reputation Level) 
Time spent in forums: 3 Months 1 Week 2 Days 4 h 33 m 45 sec
Reputation Power: 4140
Quote:
Originally Posted by Sarath_PHP
This is not an SQL base question.It may be PHP.
it's definitely not a php problem

it's actually a bad data problem
Reply With Quote
  #9  
Old March 19th, 2013, 11:12 AM
r937's Avatar
r937 r937 is offline
SQL Consultant
Click here for more information.
  
Join Date: Feb 2003
Location: Toronto Canada
Posts: 26,357 r937 User rank is General 47th Grade (Above 100000 Reputation Level)r937 User rank is General 47th Grade (Above 100000 Reputation Level)r937 User rank is General 47th Grade (Above 100000 Reputation Level)r937 User rank is General 47th Grade (Above 100000 Reputation Level)r937 User rank is General 47th Grade (Above 100000 Reputation Level)r937 User rank is General 47th Grade (Above 100000 Reputation Level)r937 User rank is General 47th Grade (Above 100000 Reputation Level)r937 User rank is General 47th Grade (Above 100000 Reputation Level)r937 User rank is General 47th Grade (Above 100000 Reputation Level)r937 User rank is General 47th Grade (Above 100000 Reputation Level)r937 User rank is General 47th Grade (Above 100000 Reputation Level)r937 User rank is General 47th Grade (Above 100000 Reputation Level)r937 User rank is General 47th Grade (Above 100000 Reputation Level)r937 User rank is General 47th Grade (Above 100000 Reputation Level)r937 User rank is General 47th Grade (Above 100000 Reputation Level)r937 User rank is General 47th Grade (Above 100000 Reputation Level) 
Time spent in forums: 3 Months 1 Week 2 Days 4 h 33 m 45 sec
Reputation Power: 4140
Quote:
Originally Posted by Sarath_PHP
SELECT DISTINCT productname FROM tablename GROUP BY productname
just a note for your future reference...

GROUP BY produces unique results, so DISTINCT here is redundant
Reply With Quote
  #10  
Old March 19th, 2013, 10:05 PM
Finance Newbie Finance Newbie is offline
Registered User
Dev Shed Newbie (0 - 499 posts)
  
Join Date: Mar 2013
Posts: 3 Finance Newbie User rank is Just a Lowly Private (1 - 20 Reputation Level) 
Time spent in forums: 3 h 16 m 22 sec
Reputation Power: 0
Right() and left()
Although I am a newbie to SQL, my instinct from other environments is that you might be able to solve the problem by first grabbing the four rightmost characters of the data using right(data,4) and then grab the left two characters of that, such as left(right(data,4),2)

I have often had to do this sort of disecting and data cleanup in Excel when people feed me crappy data.

Of course, since the data you gave us is only a sample, I realize the required cleanup could be more involved.
Reply With Quote
  #11  
Old March 20th, 2013, 01:01 AM
Sarath_PHP Sarath_PHP is offline
Registered User
Dev Shed Newbie (0 - 499 posts)
  
Join Date: Feb 2013
Posts: 20 Sarath_PHP User rank is Just a Lowly Private (1 - 20 Reputation Level) 
Time spent in forums: 6 h 11 m 46 sec
Reputation Power: 0
Hi Finance Newbie,

Thanks i got it. I include right() in to mysql query.

"SELECT productname FROM tablename GROUP BY productname ORDER BY RIGHT( productname, 4)"

Then my result is:

A4 Heavy Cover Booklets 8pp
A4 Heavy Cover Booklets 12pp
A4 Heavy Cover Booklets 16pp
A4 Heavy Cover Booklets 20pp
A4 Heavy Cover Booklets 24pp
A4 Heavy Cover Booklets 28pp
A4 Heavy Cover Booklets 80pp
Reply With Quote
  #12  
Old March 20th, 2013, 03:18 AM
dariyoosh's Avatar
dariyoosh dariyoosh is offline
Contributing User
Dev Shed Newbie (0 - 499 posts)
  
Join Date: Nov 2012
Location: Iran / France
Posts: 132 dariyoosh User rank is First Lieutenant (10000 - 20000 Reputation Level)dariyoosh User rank is First Lieutenant (10000 - 20000 Reputation Level)dariyoosh User rank is First Lieutenant (10000 - 20000 Reputation Level)dariyoosh User rank is First Lieutenant (10000 - 20000 Reputation Level)dariyoosh User rank is First Lieutenant (10000 - 20000 Reputation Level)dariyoosh User rank is First Lieutenant (10000 - 20000 Reputation Level)dariyoosh User rank is First Lieutenant (10000 - 20000 Reputation Level)dariyoosh User rank is First Lieutenant (10000 - 20000 Reputation Level) 
Time spent in forums: 2 Days 6 h 13 m 3 sec
Reputation Power: 133
Quote:
Originally Posted by r937
because ORDER BY isn't good enough

you said DESC, so 8pp should've come last instead of first


Thanks for this remark, yes in fact I made a mistake and my solution doesn't fulfil the OP's requirement.


Regards,
Dariyoosh
Reply With Quote
  #13  
Old March 20th, 2013, 09:01 AM
Finance Newbie Finance Newbie is offline
Registered User
Dev Shed Newbie (0 - 499 posts)
  
Join Date: Mar 2013
Posts: 3 Finance Newbie User rank is Just a Lowly Private (1 - 20 Reputation Level) 
Time spent in forums: 3 h 16 m 22 sec
Reputation Power: 0
Quote:
Originally Posted by Sarath_PHP
Hi Finance Newbie,

Thanks i got it. I include right() in to mysql query.

"SELECT productname FROM tablename GROUP BY productname ORDER BY RIGHT( productname, 4)"

Then my result is:

A4 Heavy Cover Booklets 8pp
A4 Heavy Cover Booklets 12pp
A4 Heavy Cover Booklets 16pp
A4 Heavy Cover Booklets 20pp
A4 Heavy Cover Booklets 24pp
A4 Heavy Cover Booklets 28pp
A4 Heavy Cover Booklets 80pp


Also, I pointed to the complex solution but forgot to think about the simple solution: If the problem is only occurring once AND you can play with the source data, maybe just go in and add an additional space or a zero in front of the eight.
Reply With Quote
  #14  
Old March 22nd, 2013, 02:13 AM
Sarath_PHP Sarath_PHP is offline
Registered User
Dev Shed Newbie (0 - 499 posts)
  
Join Date: Feb 2013
Posts: 20 Sarath_PHP User rank is Just a Lowly Private (1 - 20 Reputation Level) 
Time spent in forums: 6 h 11 m 46 sec
Reputation Power: 0
Quote:
Originally Posted by r937
just a note for your future reference...

GROUP BY produces unique results, so DISTINCT here is redundant




Thank you so much.
Reply With Quote
Reply

Viewing: Dev Shed Forums > Databases > MySQL Help > PHP-General - Ordering Numbers

« Previous Thread | Next Thread »

Developer Shed Advertisers and Affiliates



Thread Tools  Search this Thread 
Search this Thread:

Advanced Search
Display Modes  Rate This Thread 
Linear Mode Linear Mode
Rate This Thread:


Posting Rules
You may not post new threads
You may not post replies
You may not post attachments
You may not edit your posts
vB code is On
Smilies are On
[IMG] code is On
HTML code is Off
View Your Warnings | New Posts | Latest News | Latest Threads | Shoutbox
Forum Jump

Forums: » Register « |  User CP |  Games |  Calendar |  Members |  FAQs |  Sitemap |  Support | 
  
 


Powered by: vBulletin Version 3.0.5
Copyright ©2000 - 2013, Jelsoft Enterprises Ltd.

© 2003-2013 by Developer Shed. All rights reserved. DS Cluster - Follow our Sitemap