CODEWITHSUNDEEP
sql-server
Anuj Thite
Anuj Thite

Reputation: 75

Display few specific rows always at the top

I want to display a few specific Rows always at top of the query results.

For example: Cities Table. Columns: City ID, City Name

I want to fetch Query result where Mumbai, Bangalore, Chennai, Hyderabad should display at the top always.

1st way:

I can insert these records first in the table so that they will get displayed always at the top.

But, this way will not work if any other city gets added after a few months that I also want to display at the top.

Upvotes: 0

Views: 1431

Answers (3)

KumarHarsh
KumarHarsh

Reputation: 5094

Clean way of doing this,

Declare @t table (cityID int,cityname nvarchar(50))
Declare @DesireOrder table (id int identity,CityID int) -- instead of cityname

insert into @DesireOrder values (6),(3),(5),(1)

insert into @t values (2,'Gujrat')
insert into @t values (4,'Surat')
insert into @t values (6,'Mumbai')
insert into @t values (3,'Bangalore')
insert into @t values (5,'Chennai')
insert into @t values (1,'Hyderabad')
insert into @t values (8,'Delhi')
insert into @t values (7,'New Delhi')

select t.* from @t t
left join DesireOrder O on t.cityid=O.cityid
order by o.id,t.cityID

Main idea is @DesireOrder, rest you can implement as per your requirement.

Upvotes: 0

Zohar Peled
Zohar Peled

Reputation: 82474

Use an iif in your order by clause:

SELECT CityId, CityName
FROM Cities
ORDER BY IIF(CityName IN ('Mumbai', 'Bangalore', 'Chennai', 'Hyderabad'), 0, 1), CityName

You can't rely on the order in which you've entered the records to the table, because database tables are unsorted by nature, and without an order by clause, the order of the result set will be arbitrary.
For more information, read The “Natural order” misconception on my blog.

Upvotes: 3

Red Devil
Red Devil

Reputation: 2393

Try this:

Declare @t table (cityID int,cityname nvarchar(50))

insert into @t values (2,'Gujrat')
insert into @t values (4,'Surat')
insert into @t values (6,'Mumbai')
insert into @t values (3,'Bangalore')
insert into @t values (5,'Chennai')
insert into @t values (1,'Hyderabad')


select * from @t
order by case when cityname in ('Mumbai','Bangalore','Chennai','Hyderabad') then 0 else 1 END

Upvotes: 0

Related Questions