Cast - SQL Server Forums

Please start any new threads on our new site at https://forums.sqlteam.com. We've got lots of great SQL Server experts to answer whatever question you can come up with.

All Forums

General SQL Server Forums

New to SQL Server Programming

Cast

Author

Topic

suzie
Starting Member

15 Posts

Posted - 2010-03-12 : 06:11:49

Dear Friends,
I have a table that contains a field called "title_first" with Type ntext (16). I want to:

"Select distinct title_first from table"

but "Select distinct" doesn't work with ntext, so I used cast to cast ntext to nvarchar, and I did:

"SELECT DISTINCT Cast(title_first as nVarchar(150)) as title from table"

but this doesn't work, no syntax error, but it doesn't output them as distinct,

Please any help, because I need the title_first to appear once if there is more than one are the same

I have Microsoft SQL version 8...
and am reading the queries in php
Best Regards,

visakh16
Very Important crosS Applying yaK Herder

52326 Posts

Posted - 2010-03-12 : 10:47:43

not sure how you used that but DISTINCT wont work with ntext. You can convert them to nvarchar(max) if you're using SQL 2005 or above

------------------------------------------------------------------------------------------------------
SQL Server MVP
http://visakhm.blogspot.com/

madhivanan
Premature Yak Congratulator

22864 Posts

Posted - 2010-03-15 : 03:00:43

<<
I have Microsoft SQL version 8...
>>

Can you post the result of this?

SELECT @@VERSION

Madhivanan

Failing to plan is Planning to fail

suzie
Starting Member

15 Posts

Posted - 2010-03-15 : 07:40:48

thanks for your help,
ok I changed the type to varchar, and it works..
but the problem now that in my table I have for example 2 rows:
title_first | priority

suzy | 1

suzy | 2

I want to select distinct for the first column only, I.e:
if I put select distinct title_first, priority from table, it gives me the 2 occurences of "suzy"...

and I have to print the priority because I need it, so please what shall I do in this case????

haroon2k9
Constraint Violating Yak Guru

328 Posts

Posted - 2010-03-15 : 07:44:04

quote:
Originally posted by suzie

thanks for your help,
ok I changed the type to varchar, and it works..
but the problem now that in my table I have for example 2 rows:
title_first | priority

suzy | 1

suzy | 2

I want to select distinct for the first column only, I.e:
if I put select distinct title_first, priority from table, it gives me the 2 occurences of "suzy"...

and I have to print the priority because I need it, so please what shall I do in this case????

Try this.
select title_first,priority from
(
select title_first,priority ,seq=row_number()over(partition by title_first order by priority desc) from tbl
)as t where seq=1

suzie
Starting Member

15 Posts

Posted - 2010-03-15 : 08:34:35

it gives me this error:

SQL error: [Microsoft][ODBC SQL Server Driver][SQL Server]'row_number' is not a recognized function name., SQL state 37000 in SQLExecDirect in

madhivanan
Premature Yak Congratulator

22864 Posts

Posted - 2010-03-15 : 08:37:04

Did you see my first reply?

Madhivanan

Failing to plan is Planning to fail

haroon2k9
Constraint Violating Yak Guru

328 Posts

Posted - 2010-03-15 : 08:40:14

quote:
Originally posted by suzie

it gives me this error:

SQL error: [Microsoft][ODBC SQL Server Driver][SQL Server]'row_number' is not a recognized function name., SQL state 37000 in SQLExecDirect in

Please reply for this first.

quote:

<<
I have Microsoft SQL version 8...
>>

Can you post the result of this?

SELECT @@VERSION

Madhivanan

Failing to plan is Planning to fail

suzie
Starting Member

15 Posts

Posted - 2010-03-15 : 08:44:42

Sorry for the late...

here is the result:
Microsoft SQL Server 2000 - 8.00.194 (Intel X86)
Aug 6 2000 00:57:48
Copyright (c) 1988-2000 Microsoft Corporation
Personal Edition on Windows NT 5.1 (Build 2600: Service Pack 3)

haroon2k9
Constraint Violating Yak Guru

328 Posts

Posted - 2010-03-15 : 09:03:44

quote:
Originally posted by suzie

Sorry for the late...

here is the result:
Microsoft SQL Server 2000 - 8.00.194 (Intel X86)
Aug 6 2000 00:57:48
Copyright (c) 1988-2000 Microsoft Corporation
Personal Edition on Windows NT 5.1 (Build 2600: Service Pack 3)

select (select count(*) from tbl as t1
where t1.title_first<= t2. title_first) as seq, t2. title_first
from tbl as t2 order by priority desc

suzie
Starting Member

15 Posts

Posted - 2010-03-15 : 09:17:47

sorry if i am bothering, but here is another error:

SQL error: [Microsoft][ODBC SQL Server Driver][SQL Server]The column prefix 't1' does not match with a table name or alias name used in the query.,

haroon2k9
Constraint Violating Yak Guru

328 Posts

Posted - 2010-03-15 : 09:27:26

quote:
Originally posted by suzie

sorry if i am bothering, but here is another error:

SQL error: [Microsoft][ODBC SQL Server Driver][SQL Server]The column prefix 't1' does not match with a table name or alias name used in the query.,

Typo mistake.sorry
please run this..change into according to y.if any issues write back to us.HTH

declare @t table

(
pk int identity(1,1),
fname varchar(50),
pri int
)

insert @t
select 'a',1 union all
select 'b',1 union all
select 'a',2 union all
select 'b',2 union all
select 'b',3

select * from @t

select t1.fname,
(select count(*) from @t where fname=t1.fname and pk <=t1.pk)
from @t t1

visakh16
Very Important crosS Applying yaK Herder

52326 Posts

Posted - 2010-03-15 : 09:39:05

when you retrieve distinct values of title_first field what should be associated priority value fetched?

------------------------------------------------------------------------------------------------------
SQL Server MVP
http://visakhm.blogspot.com/

suzie
Starting Member

15 Posts

Posted - 2010-03-15 : 09:57:22

Hi,
thanks a lot for your help but am little bit confused and lost here,
please can u give me the query concerning my table:

my table is called "adab"
and it has title_first (varchar)
and priority (int)
what I want is:
select (distinct title_first), priority from adab order by priority

distinct only for title_first

thanks a lot for you

visakh16
Very Important crosS Applying yaK Herder

52326 Posts

Posted - 2010-03-15 : 10:03:43

quote:
Originally posted by suzie

Hi,
thanks a lot for your help but am little bit confused and lost here,
please can u give me the query concerning my table:

my table is called "adab"
and it has title_first (varchar)
and priority (int)
what I want is:
select (distinct title_first), priority from adab order by priority

distinct only for title_first

thanks a lot for you

oK agreed. but before that answer. which associated value for priority you want to return with distinct values of title_first? the least, maximum,first or last occuring priority value?

------------------------------------------------------------------------------------------------------
SQL Server MVP
http://visakhm.blogspot.com/

Lamprey
Master Smack Fu Yak Hacker

4614 Posts

Posted - 2010-03-15 : 11:17:10

Not sure which priority you want, so you might need to change MAX to MIN or something else, but...

SELECT title_first, MAX(piority) AS priority
FROM adab
GROUP BY title_first

suzie
Starting Member

15 Posts

Posted - 2010-03-16 : 03:07:27

ok, it works now...Thanks a lot a lot....

visakh16
Very Important crosS Applying yaK Herder

52326 Posts

Posted - 2010-03-16 : 04:00:05

quote:
Originally posted by suzie

ok, it works now...Thanks a lot a lot....

keep in mind that suggestion provided just returns you max value of priority field without considering any order

------------------------------------------------------------------------------------------------------
SQL Server MVP
http://visakhm.blogspot.com/

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources