[SOLVED] ORDER BY help, need to first list similar together and then order by price

[skinny]

Hello,

I've spent wAy too long trying to figure this out. I know I'm missing some rediculously minor detail but everything I've tried has not worked.

I have a table that I need to sort in two ways. I first need to put/list all of the matching names together and then sort them by price. For example:

nameA = $10

nameB = $15

nameC = $30

nameA = $5

nameB = $20

nameC = $25

 

needs to result as:

nameA = $5

nameA = $10

nameB = $15

nameB = $20

nameC = $25

nameC = $30

 

My thoughts were that the following should work:

SELECT * FROM $usertable ORDER BY unit DESC, price DESC

 

So to recap. All I'm trying to do is get my results to match fields together and then order them by price.

 

Any help will be very much appreciated!

[Maq]

What do you actually get?  If the field is a VARCHAR it will sort alphabetically.

[skinny]

My actual resutls are as follows when using (SELECT * FROM $usertable ORDER BY unit,price DESC)

 

UNIT              PRICE

105 Third St #1 - $1,381,000

105 Third St #2 - $761,000

105 Third St #3 - $769,500

105 Third St #4 - $311,500

105 Third St #5 - $670,500

107 Third St #1 - $1,381,000

107 Third St #2 - $761,000

107 Third St #3 - $769,500

107 Third St #4 - $311,500

107 Third St #5 - $710,500

 

What I need is:

UNIT              PRICE

105 Third St #4 - $311,500

105 Third St #5 - $670,500

105 Third St #2 - $761,000

105 Third St #3 - $769,500

105 Third St #1 - $1,381,000

107 Third St #4 - $311,500

107 Third St #5 - $710,500

107 Third St #2 - $761,000

107 Third St #3 - $769,500

107 Third St #1 - $1,381,000

 

Thanks for replying!

[corbin]

It's ordering it that way because it does it alphabetically.

 

The easiest solution is to get rid of the $ and commas and make it a numeric field.

[skinny]

The dolloar sign and commas are added after are not in the actual data but the field was VARCHAR. I just changed it to INT but I'm not seeing any change in the results. Do I need to re-enter the data fot that field to make it register as numeric?

[skinny]

If I order by 'unit' or 'price' seperately the results are ordered correctly but once I try to order by 'unit' first (essentially trying to group them) and then order by 'price' it does't work.

 

What I need to do is group all of the 'unit's together and then order them in their group by 'price'.

 

Appreciating your help!

[PFMaBiSmAd]

SELECT * FROM $usertable ORDER BY unit,price DESC

 

That means order by price descending (i.e. highest first.) To get the results you listed, remove the DESC.

 

 SELECT * FROM $usertable ORDER BY unit,price

[skinny]

No matter what I do the second ORDER gets ingnored and it only orders by the first field.

 

The following orders by 'unit' and ignores 'price':

SELECT * FROM $usertable ORDER BY unit,price

 

The following orders by 'price' and ignores 'unit':

SELECT * FROM $usertable ORDER BY price,unit

 

I've tried adding DESC and ASC to end of each field (ex: price ASC) then one and not the other but the second field (unit,price) gets ignored from sorting. I feel like I've done this kind of sorting before but nothing's working. Seems so simple.

 

My hair is almost gone 

[PFMaBiSmAd]

If the price is actually a numeric data type, it will order correctly.

 

What exactly is in the unit column and in the price column? If the text 'Third St #x' is part of the unit, there is no way it will order any differently than what you are currently getting because the x = 1,2,3,4,5 is part of what ORDER BY unit is using, so there is only one price per unique unit.

[skinny]

I was afraid of that! So without breaking the data apart (creating a seperate field for the #x) is there a way I can group the units together (displaying them all of course) and then order by price?

 

I tried the following in hopes of just reading the first 6 characters of the 'unit' (to ommit the #x) but with no luck:

SELECT * FROM $usertable ORDER BY SUBSTR(unit,1,6),price

 

Isn't there something similar I could do without breaking the table into more fields?

[PFMaBiSmAd]

If the first 6 characters of 'unit' are what you want to 'group' together and they sort correctly as strings, then it should work.

 

You are going to need to be way more specific about what you are getting when it does not work and what your actual data is that is not working as expected.

 

Edit: If some portion of that is an address and some portion is a unit number at that address, then yes, the best solution for all time is to use separate columns for the address and the unit number.

[skinny]

sorry for my following outburst:

halalooooooya!!!!!!!!!!!!

with the turning the price field into an integer and using SUBSTR(unit,1,6) it DID work  :

SELECT * FROM $usertable ORDER BY SUBSTR(unit,1,6),price

 

WOW... I think I spent at least 5 hours on that... rediculous

 

What you were saying was exactly true "If the text 'Third St #x' is part of the unit, there is no way it will order any differently than what you are currently getting because the x = 1,2,3,4,5 is part of what ORDER BY unit is using, so there is only one price per unique unit.". So using substr to get match the fist characters of the field PLUS changing the field from VARCHAR to INT worked.

 

I can't thank you enough. I don't think I would have ever gone to sleep 

 

Thanks again for your time!