April 14, 2003 at 11:25 pm
Hi
I have a sql query which is "select name,City from Location order by Country"
Now I have upto 4 different countries in the location table. so giving the above
command will give the data sorted on country ascending type and typing desc will give
me descending type of sort.
But I don't want either of the two. I want to sort it in such a way that the country displayed first should be customisable.Like say the country displayed 2nd in the above sort should be my 1st.
How can I achieve this.
Thank you.
April 15, 2003 at 1:37 am
One way you could do this is to put the countries into a temporary table, with an idenitity column, in the order in which you want them ordered by and include the temp table in the query.
create table #country
(sortorder int identity, Country varchar(255) )
insert #country
(country)
values
(@country)
select name,City
from Location a, #country b
where a.country = b.country
order by b.sortorder
Jeremy
April 15, 2003 at 3:03 am
If you know the sequence you want the countries to apprear in, then use a CASE statement to define the order e.g.
select Street,City,Country,
case Country
when 'United Kingdom' then 1
when 'France' then 2
when 'USA' then 3
when 'Mexico' then 4
else 5
end
as SortOrder
from Location
order by SortOrder
April 17, 2003 at 8:45 am
You could also have a Country sorting table with two columns (Country and sortorder). For complete normalization, you could throw in an ID (for space consideration) and use that for joining with the other tables. This also helps prevent typoes. (I think we had 5 different spellings of Utah)
my $.02
Guarddata-
Viewing 4 posts - 1 through 3 (of 3 total)
You must be logged in to reply to this topic. Login to reply