Want to protect your cyber security and still get fast solutions? Ask a secure question today.Go Premium

x
  • Status: Solved
  • Priority: Medium
  • Security: Public
  • Views: 503
  • Last Modified:

Sort Order QUESION

Order by Clause produces these results:

Jackson Center, OH
Jackson Center, PA
Jackson Heights, NY
Jackson Springs, NC
Jackson, AL


It should be:

Jackson, AL
Jackson Center, OH
Jackson Center, PA
Jackson Heights, NY
Jackson Springs, NC



How do I fix this issue?
0
poweraddict
Asked:
poweraddict
  • 4
  • 2
1 Solution
 
Neil RussellTechnical Development LeadCommented:
My first comment would be "Why are you storing two seperate pieces of information in a single field"?

Assuming that the data you show comes from a single field then that is the start of your problem.  If the State initials were stored in a seperated table and referenced by a key And your City stored in a City table and referenced by its key then you could neatly sort by City and get the desired results easily.

Its all down to database design.
0
 
Neil RussellTechnical Development LeadCommented:
As an example, assume you have your table PLUS one called tblCities and one called tblState

tblCities has two fileds
PK int pkCityID
varchar CityName

tblState has two fields
PK int pkStateID
varchar StateName

Now your main table contains the pkStateID and the pkCityID of the relevant City and State.

So you can use

SELECT * from MyTable, tblCities, tblState
WHERE mytable.pkCityID = tblCities.pkCityID
AND mytable.pkStateID = tblStateID
ORDER BY tblCities.pkCityID, tblStates.pkStateID

(Rough as im in bed with raging tooth ache sorry)
0
 
poweraddictAuthor Commented:
I have the cities, states and zip codes in one table in separate columns.

I created a new column with the cities and states combined for an autocomplete feature on my website.

SELECT DISTINCT TOP 12 AutoComplete FROM ZIP WHERE AutoComplete LIKE 'jack%'

I added the new column because it could be indexed for quicker autocomplete dropdown rendering and it wouldn't dissapear after the user enters past the comma.


For example all the cities are in the autocomplete column
Jackson Center, OH
Jackson Center, PA
Jackson Heights, NY
Jackson Springs, NC
Jackson, AL
0
Independent Software Vendors: We Want Your Opinion

We value your feedback.

Take our survey and automatically be enter to win anyone of the following:
Yeti Cooler, Amazon eGift Card, and Movie eGift Card!

 
Neil RussellTechnical Development LeadCommented:
In that case all you need to do is.....

SELECT DISTINCT TOP 12 AutoComplete
FROM ZIP
WHERE AutoComplete LIKE 'jack%'
ORDER BY City

And ensure that you have City Indexed.
0
 
Guy Hengel [angelIII / a3]Billing EngineerCommented:
it does order like this because the space comes before the comma.

ORDER BY replace(yourcol, ',', ' ')

and your ordering might become "better" ...
0
 
poweraddictAuthor Commented:
thank you
0
 
Neil RussellTechnical Development LeadCommented:
A simpler solution would have been to create this field as "AL Jackson" "PA Jackson Center"
Etc and get your users to enter state followed by city
0

Featured Post

Technology Partners: We Want Your Opinion!

We value your feedback.

Take our survey and automatically be enter to win anyone of the following:
Yeti Cooler, Amazon eGift Card, and Movie eGift Card!

  • 4
  • 2
Tackle projects and never again get stuck behind a technical roadblock.
Join Now