How to put a record on top in Order by Clause in SQL Server

Ikramsha...

SOLVED CLOSED ACTIVE ANSWERED
User: Ikramshams
Posted: on Dec 31, 2012 05:24 AM
Forum: SQL Server
Answer: 2
Views: 3038

I am writing this query.

select DeptId,Departmentname from Departmentname order by departmentname

According to this query the result is:

14	Academy 
1	Administrator
4	Advertising Dept
21	All
17	Exporting Dept
6	Finance Dept
13	HR Dept
3	IT Dept
20	Production Dept
18	Purchasing Dept
19	Research and development Dept
16	Sales Dept
15	Technical support Dept

I want 'All' on the top and after that Alphabetical(order by DepartmentName). like below i want result.

For this how i will write the query. Please suggest me.

21	All

14	Academy 
1	Administrator
4	Advertising Dept
17	Exporting Dept
6	Finance Dept
13	HR Dept
3	IT Dept
20	Production Dept
18	Purchasing Dept
19	Research and development Dept
16	Sales Dept
15	Technical support Dept

Download FREE API for Word, Excel and PDF in ASP.Net

Mudassar

0 3

Reply
ANSWER
User: Mudassar
Replied: on Dec 31, 2012 05:48 AM
Report

You will need to make use of Temp Table to get this output

--Generating Sample data
declare @tbl Table(Id int, Dept varchar(30))

insert into @tbl
select
14,	'Academy'
union all
select
1,	'Administrator'
union all
select
4,	'Advertising Dept'
union all
select
21,	'All'
union all
select
17,	'Exporting Dept'
union all
select
6,	'Finance Dept'
union all
select
13,	'HR Dept'
union all
select
3,	'IT Dept'
union all
select
20,	'Production Dept'
union all
select
18,	'Purchasing Dept'
union all
select
19,	'Research and development Dept'
union all
select
16,	'Sales Dept'
union all
select
15,	'Technical support Dept'



---Actual Query Starts here

declare @Table Table([auto] int IDENTITY(1,1) NOT NULL, Id int, Dept varchar (30))

insert into @Table
select Id, Dept from @tbl where Id = 21

insert into @Table
select Id, Dept from @tbl where Id <> 21
order by Dept Asc

Select Id, Dept from @Table order by [auto]

Ikramsha...

0 4

Reply
ANSWER
User: Ikramshams
Replied: on Jan 01, 2013 12:45 AM
Report

Thanks for your kind support. Now it is working very fine. I have created the procedure like below:

Create Procedure Pro_FDeptBind

Begin

Declare @Dept TABLE([auto] int IDENTITY(1,1) NOT NULL,

DeptId int,

DepartmentName varchar(100),

IsActive char(1) DEFAULT 0,

Flag char(1) DEFAULT 0,

CDate DateTime DEFAULT GETDATE()

)

insert into @Dept(DeptId,DepartmentName,IsActive,Flag,CDate)

select d.DeptId,d.DepartmentName,d.IsActive,d.Flag,d.CDate from DepartmentName d where d.DeptId=21

insert into @Dept(DeptId,DepartmentName,IsActive,Flag,CDate)

select d.DeptId,d.DepartmentName,d.IsActive,d.Flag,d.CDate from DepartmentName d where d.DeptId<>21 order by d.DepartmentName Asc

Select dn.DeptId,[auto],Case dn.DepartmentName when 'All' then

dn.DepartmentName + ' ('+ Convert(varchar(50),(Select Count(*) from Jobdetails where IsActive=0))+')'

else

dn.DepartmentName + ' ('+ Convert(varchar(50),Count(jd.DeptId))+')'

End as DepartmentName

from @Dept dn LEFT OUTER JOIN jobdetails jd

on dn.DeptId=jd.DeptId where dn.IsActive=0

and flag=0 and dn.DepartmentName is not null GROUP by dn.DeptId,dn.DepartmentName ,[auto]

order by [auto]

End

I agree, here is the link: https://www.e-iceblue.com/Introduce/spire-office-for-net-free.html

How to put a record on top in Order by Clause in SQL Server

Company

Explore

Follow us

Contact Us