.NET Tutorials, Forums, Interview Questions And Answers
Welcome :Guest
 
Sign In
Register
 
Win Surprise Gifts!!!
Congratulations!!!


Top 5 Contributors of the Month
sivanagamahesh

Post New Resource Bookmark and Share   

Display favorite records in top of a list

Posted By:Kemal AL GAZZAH       Posted Date: November 29, 2015    Points: 40    Category:    URL: http://www.dotnetspark.com  

If you have for a example a drop down of world countries and you want to display in top some favourite countries you use frequently, here solution in sql server
 

I have a table of countries for example which I use to populate a dropdown
If I display the countries in the dropdown ordered by country name then it is possible that I will get in top some countries which I don't use frequently and I have to scroll down to find the country I want to use
here some solution in sql server to display in top some favourite countries without the need to store them in a particular temporary tabel and without the need to add additional columns like favourite tag to the country table.

I extract the world countries list from this link

Create the table country
if exists(select 1 from sysobjects where name='country') drop table country
go
create table country(idcountry int identity(1,1),code int, alpha2 varchar(2), alpha3 varchar(3),countryname nvarchar(100),countryname_en nvarchar(100))

My favourite countries are for exmaple
1.Tunisia
2.Algeria
3.France 
4.Morocco
5.Libya
6.Senegal

then I will display the rest of countries by alphabetic order
NB: the country name here are in french

Solution 1
select idcountry,countryname from country
 order by
case countryname 
when 'Tunisie'  then '1'
when 'Algérie'   then '2'
when 'France'   then '3'
when 'Maroc'    then '4'
when 'Libye'     then '5'
when 'Sénégal' then '6'
else '999' end,countryname;

Solution 2
select idcountry,countryname
 from country
order by 
10*charindex('Tunisie',countryname,1)
+9*charindex('Algérie',countryname,1)
+8*charindex('France',countryname,1)
+7*charindex('Maroc',countryname,1)
+6*charindex('Libye',countryname,1)
+5*charindex('Sénégal',countryname,1) desc,
countryname

Solution 3

select 1 "myorder",'Tunisie' "countryname"
union
select 2 "myorder",'Algérie' "countryname"
union
select 3 "myorder",'France' "countryname"
union
select 4 "myorder",'Maroc' "countryname"
union
select 5 "myorder",'Libye' "countryname"
union
select 5 "myorder",'Sénégal' "countryname"
union
select 999 "myorder",countryname from country where countryname not in ('Tunisie','Algérie','France','Maroc','Libye','Sénégal')
order by myorder ,countryname







     

Further Readings:

Responses

No response found. Be the first to respond this post

Post Comment

You must Sign In To post reply
Here

Hall of Fame    Twitter   Terms of Service    Privacy Policy    Contact Us    Archives   Tell A Friend