Go Premium for a chance to win a PS4. Enter to Win

x
?
Solved

sort text column

Posted on 2011-02-24
3
Medium Priority
?
540 Views
Last Modified: 2012-06-21
CREATE TABLE [dbo].[sitedata](
      [productid] [int] NULL,
      [pathid] [int] NULL,
      [accessoryid] [int] NULL,
      [alternatecolors] [varchar](100) COLLATE SQL_Latin1_General_CP1_CI_AS NULL,
      [description] [text] COLLATE SQL_Latin1_General_CP1_CI_AS NULL,
      [specifications] [text] COLLATE SQL_Latin1_General_CP1_CI_AS NULL,
      [features] [text] COLLATE SQL_Latin1_General_CP1_CI_AS NULL,
      [itemincludes] [text] COLLATE SQL_Latin1_General_CP1_CI_AS NULL,
      [accessories] [text] COLLATE SQL_Latin1_General_CP1_CI_AS NULL,
      [infoandguides] [text] COLLATE SQL_Latin1_General_CP1_CI_AS NULL,
      [rebateinfo] [text] COLLATE SQL_Latin1_General_CP1_CI_AS NULL,
      [sunshineactive] [int] NULL,
      [sunshineprice] [money] NULL,
      [sunshinefeed] [int] NULL,
      [pyxisactive] [int] NULL,
      [pyxisprice] [money] NULL,
      [pyxisfeed] [int] NULL,
      [hdewactive] [int] NULL,
      [hdewprice] [money] NULL,
      [hdewfeed] [int] NULL,
      [shippingtype] [varchar](50) COLLATE SQL_Latin1_General_CP1_CI_AS NULL,
      [shippingpriceground] [money] NULL,
      [shippingprice3day] [money] NULL,
      [shippingprice2day] [money] NULL,
      [shippingprice1day] [money] NULL,
      [shippingpricetruck] [money] NULL,
      [shippingpricetruckexpress] [money] NULL,
      [shippingpricetruckwhite] [money] NULL,
      [pyxispackages] [varchar](1000) COLLATE SQL_Latin1_General_CP1_CI_AS NULL,
      [sunpackages] [varchar](1000) COLLATE SQL_Latin1_General_CP1_CI_AS NULL,
      [ebaydescription] [text] COLLATE SQL_Latin1_General_CP1_CI_AS NULL,
      [pdescription] [text] COLLATE SQL_Latin1_General_CP1_CI_AS NULL
) ON [PRIMARY] TEXTIMAGE_ON [PRIMARY]



select * from sitedata
order by accessories desc

Msg 306, Level 16, State 2, Line 1
The text, ntext, and image data types cannot be compared or sorted, except when using IS NULL or LIKE operator.


select * from sitedata
where accessories are not null
Msg 4145, Level 15, State 1, Line 2
An expression of non-boolean type specified in a context where a condition is expected, near 'are'.





this is what one column has it in it for one row
147276,146588,147137,146586,147371,148941,148942,148943,148944,148945,3770,148951,2057,148899,148934,148938,3652,148918,148914,148921,31701,147445,148929,148930,148927,148928,148926,53,21063,146066,147141,147213,147372,147373,147290,145762,1079,57,147191,147423,54,55,147139
0
Comment
Question by:rgb192
3 Comments
 
LVL 32

Expert Comment

by:Ephraim Wangoya
ID: 34974359

You can not sort using a field of type  [text]
You can change the field to a long varchar
0
 
LVL 22

Accepted Solution

by:
pivar earned 2000 total points
ID: 34974415
Hi,

You can't order a text field however you can convert it to a varchar(max)

like


select * from sitedata
order by convert(varchar(max), accessories) desc

the second statement should read

select * from sitedata
where accessories is not null

/peter

0
 

Author Closing Comment

by:rgb192
ID: 34975512
thanks
0

Featured Post

Free Tool: Site Down Detector

Helpful to verify reports of your own downtime, or to double check a downed website you are trying to access.

One of a set of tools we are providing to everyone as a way of saying thank you for being a part of the community.

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

INTRODUCTION: While tying your database objects into builds and your enterprise source control system takes a third-party product (like Visual Studio Database Edition or Red-Gate's SQL Source Control), you can achieve some protection using a sing…
Use this article to create a batch file to backup a Microsoft SQL Server database to a Windows folder.  The folder can be on the local hard drive or on a network share.  This batch file will query the SQL server to get the current date & time and wi…
Video by: ITPro.TV
In this episode Don builds upon the troubleshooting techniques by demonstrating how to properly monitor a vSphere deployment to detect problems before they occur. He begins the show using tools found within the vSphere suite as ends the show demonst…
Look below the covers at a subform control , and the form that is inside it. Explore properties and see how easy it is to aggregate, get statistics, and synchronize results for your data. A Microsoft Access subform is used to show relevant calcul…

916 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question