how to sort based on values..

# how to sort based on values..

• Saturday, January 26, 2013 6:39 PM

Hi Friends ,

Can U pls let me know how to sort based on values..

In my scenario need to show values in alphabetical order , if value contains ''A'' , then show them at bottom of the report ..

Column

-----------

B

A

R

Ac

D

Ab

Expected o/p

--------------

B

D

R

A

Ab

Ac

### All Replies

• Saturday, January 26, 2013 9:11 PM

Hi Koti,

The below code snippet will give you the desired output...but i should this is not a good way of coding. So try to optimize it from your end or take help from others too. If i'll get the optimized one will let you know :)

CREATE TABLE [dbo].[SortBasedOnValues](
[ColumnA] [nvarchar](50) NULL
) ON [PRIMARY]
GO
INSERT [dbo].[SortBasedOnValues] ([ColumnA]) VALUES (N'B')
INSERT [dbo].[SortBasedOnValues] ([ColumnA]) VALUES (N'A')
INSERT [dbo].[SortBasedOnValues] ([ColumnA]) VALUES (N'R')
INSERT [dbo].[SortBasedOnValues] ([ColumnA]) VALUES (N'Ac')
INSERT [dbo].[SortBasedOnValues] ([ColumnA]) VALUES (N'D')
INSERT [dbo].[SortBasedOnValues] ([ColumnA]) VALUES (N'Ab')

-----------------------------------------------------------

declare @maxrownumber int
;WITH CTE1
as
(
Select ColumnA, ROW_NUMBER() over ( order by ColumnA) as RowNum1
from SortBasedOnValues
)
, CTE2
as
(
Select ColumnA, ROW_NUMBER() over ( order by ColumnA) as RowNum2
from SortBasedOnValues
Where ColumnA not like 'A%'
)
, CTE3 as
(
Select ColumnA, ROW_NUMBER() over ( order by ColumnA) as RowNum3
from SortBasedOnValues
where columnA like 'A%'
)
, CTE4 as
(
Select maxrownumbervalue = MAX(RowNum2)
from CTE2
)
, CTE5 as
(
Select
CTE1.ColumnA as ColumnA,
(Select maxrownumbervalue = MAX(RowNum2) from CTE2) + CTE3.RowNum3 as C
from CTE1 inner join CTE3 on CTE1.RowNum1= CTE3.RowNum3
group by CTE1.ColumnA, CTE1.RowNum1, CTE3.RowNum3
)
, CTE6 as
(
Select * from CTE2
union
Select * from CTE5

)
Select ColumnA from CTE6
order by RowNum2

• Sunday, January 27, 2013 4:36 AM

select * from #SortBasedOnValues
order by case when [ColumnA] like 'A%' THEN [ColumnA] END , [ColumnA] ASC

Best Regards,Uri Dimant SQL Server MVP, http://sqlblog.com/blogs/uri_dimant/

MS SQL optimization: MS SQL Development and Optimization
MS SQL Blog: Large scale of database and data cleansing
Remote DBA Services: Improves MS SQL Database Performance

• Tuesday, January 29, 2013 3:04 AM

Hi Uri where I need to write this query???
• Thursday, January 31, 2013 2:09 AM
Moderator

Hi Kotim,

You can use the query Uri post above as dataset query in your report.