Sorting of "Text" Datatype

Question

"Select *, SUBSTRING((SELECT ',' + s.product FROM DiscountedWarrantiesOffersProducts s where idoffer=d.id ORDER BY s.product FOR XML PATH('')),2,200000) as products from DiscountedWarrantiesOffers d"

 

This is the query from  I am getting the Data on Gridview.

I want to sort the fields in that gridview.

One field is Text datatype.

 

Kindly suggest the query.

 Akash

 

Answer 1

which field? I guess products?

 

if so, then you need to place the current select query in a bracket, give it an alias and then order by that alias's column

 

e.g.

 

select * from

(Select *, SUBSTRING((SELECT ',' + s.product FROM DiscountedWarrantiesOffersProducts s where idoffer=d.id ORDER BY s.product FOR XML PATH('')),2,200000) as products from DiscountedWarrantiesOffers d) t

order by t.products

Answer 2

please clarify what solution you want?

 

do you want to sort column in SQL query?

 

or you want to sort in gridview?

 

your question category is SQL server, so change it too if not expecting answer in SQL

Answer 3

Hi Nilesh,

 

I want to sort the column in SQL query.

 

Answer 4

If sortExpression = "offerdescription" Then
strSql = "Select *, CONVERT(nvarchar, offerdescription) AS OfferDescription1 from FinanceOffers"
Else
strSql = "Select * from FinanceOffers"
End If

If sortExpression = "" Then
strSql &= " order by offerid "
Else
If sortExpression = "offerdescription" Then
sortExpression = "offerdescription1"
End If
strSql &= " order by " & sortExpression
If sortDirection = 1 Then
strSql &= " DESC "
Else
strSql &= " ASC "
End If

End If

 

 

This is the code which I used for the sorting for another table.  The above query is for this gridview.

 

"offerdescription" is the column header which is text datatype in the DB.