sort text column

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
LVL 1
rgb192Asked:
Who is Participating?

Improve company productivity with a Business Account.Sign Up

x
 
pivarConnect With a Mentor Commented:
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
 
Ephraim WangoyaCommented:

You can not sort using a field of type  [text]
You can change the field to a long varchar
0
 
rgb192Author Commented:
thanks
0
Question has a verified solution.

Are you are experiencing a similar issue? Get a personalized answer when you ask a related question.

Have a better answer? Share it in a comment.

All Courses

From novice to tech pro — start learning today.