Use of Order by on value not column. Need help

Question

Use of Order by on value not column. Need help

pirzadaz ali

Valued Member

Points: 68
More actions
April 6, 2007 at 2:32 am

#119539

Hi,
I have a dropdown filled with city names. The tables which is being searched has City column with names of cities for the records to be pulled. Now problem is I need to Order by records with the value from the drop down which is city name and can n't use order by on "City" column in the DB. Any idea how to do this please.
Note: Order by use on the value not on the column.

thanks

Viewing 7 posts - 1 through 6 (of 6 total)

You must be logged in to reply to this topic. Login to reply

Botond SSC Eights! Points: 838 More actions · Answer 1

Could you be more specific? Or could you provide some example?

It seems, that basically you are saying: "I need to order a table, but I cannot use order by on the column which is the only one containg the necessary information for the ordering".

Am I right, or I am missing something?

🙂

pirzadaz ali Valued Member Points: 68 More actions · Answer 2

Suppose I have a table called City_Table

and there is column called XCode and City and data is like

XCode City

A Boaz

B Arab

C Calera

D Cordova

E FairField

F Arab

G Boaz

H Calera

I FairField

J Cordova

H FairField

Now what I want is to run the select and want to FairField listed top than other records at the bottom like

E FairField

I FairField

H FairField

A Boaz

B Arab

C Calera

D Cordova

F Arab

G Boaz

H Calera

J Cordova

Not sure if it can be solved with order by or any other way.

Thanks

David McFarland SSChampion Points: 11815 More actions · Answer 3

If I'm understanding you correctly, then sure (untested, so likely a logic error or two):

DECLARE

@strSelectedCity varchar(50)

SET

@strSelectedCity = 'FairField'

SELECT

XCode

,City

FROM

City_Table

ORDER BY

CASE WHEN City = @strSelectedCity THEN 0 ELSE 1 END

,XCode

Edit: Note that the DECLARE/SET combination is simply a replacement for a parameter passing your value from the dropdown.

Longshanks SSC Enthusiast Points: 115 More actions · Answer 4

I do this:

DECLARE

@strSelectedCity varchar(50)

SET

@strSelectedCity = 'FairField'

SELECT

XCode, City, CASE WHEN City = @strSelectedCity THEN 0 ELSE 1 END AS Sort

FROM

City_Table

ORDER BY

Sort, XCode

Cheers.

pirzadaz ali Valued Member Points: 68 More actions · Answer 5

Hi,

Thanks for the reply. I have little problem below. Now when I use the DISTINCT in SELECT than it says that I need to use columns that are in ORDER BY be used in SELECT like blue. What I want is. Is there any way where

I remove blue from select query and just keep the RED in ORDER BY Clause using DISTINCT? or any suggestions please

SELECT DISTINCT S.XCode, S.Name, C.CategoryName, (A.CategoryName) AS City, S.Ranking, S.Logo, S.Movie, S.MemberShipType, S.Rating, S.HQAddress, S.HQCity, S.HQState, S.HQZip, S.PhoneNumber, S.URL, S.Description, S.FaxNumber, S.P1, S.NewRating, S.SPOrdering, S.SPRanking, S.ShowHideImage, S.ClassType, S.ShowHide, S.RankingStatus, S.FullAddress,

S.SPOrdering, S.SPRanking, A.CatOrdering, CASE WHEN A.CategoryName = 'Mesa' THEN 0 ELSE 1 END

FROM SPLookup S INNER JOIN CatLookup A

ON S.XCode = A.XCode

INNER JOIN #CN C

ON S.XCode = C.XCode

WHERE A.Type = 'Locations' AND A.CatOrdering = 0 AND SubCategoryId = '45'

ORDER BY S.SPOrdering, S.SPRanking, A.CatOrdering, CASE WHEN A.CategoryName = 'Phoenix' THEN 0 ELSE 1 END

David McFarland SSChampion Points: 11815 More actions · Answer 6

You didn't mention that you wanted to use DISTINCT. Without DISTINCT, my solution works fine. For DISTINCT, you need to return the sorting column, so use longshanks solution, which is identical to the one I gave you, except he returns the sorting column in the SELECT.

Be aware that there are potential "gotchas" when returning an additional column, and it's why I don't do it unless I have to. For example, if you create a table out of this, and if there is some procedure somewhere that uses this as the source for an INSERT INTO... SELECT * type statement, it will break. While people shouldn't use that syntax for in insert, it's still quite prevalent in the field.