how to sort based on values..

Question

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

Answer 1

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 

---

Please Mark as Answer if my post solved your problem or Vote As Helpful if this helps. Blogs: www.sqlserver2005forum.blogspot.com

Answer 2

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

Answer 3

Hi Uri where I need to write this query???

Answer 4

Hi Kotim,

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

If you have any question, please feel free to ask.

Regards,

Fanny Liu

---

Fanny Liu
TechNet Community Support