Cast

[suzie]

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 sameI have Microsoft SQL version 8...and am reading the queries in phpBest Regards,

[justsomeguy]

Is it better to just change the field to a varchar field? Do you really need a text field for that?

[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 | prioritysuzy | 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????

[justsomeguy]

If you're selecting distinct, then it doesn't make sense to select the priority. There are multiple values of priority for a title, so it doesn't make sense to select only one, how does it know which priority you want?If you want to select the max, or average, or sum, or a count or whatever then you can use GROUP BY instead of distinct. It's hard to give suggestions without knowing what you're going for though, but it doesn't make sense to tell the database that you want a distinct value from one column, and a value from another when the other might have multiple values. The database doesn't know which value to get, so it returns both of them.

[suzie]

If you're selecting distinct, then it doesn't make sense to select the priority. There are multiple values of priority for a title, so it doesn't make sense to select only one, how does it know which priority you want?If you want to select the max, or average, or sum, or a count or whatever then you can use GROUP BY instead of distinct. It's hard to give suggestions without knowing what you're going for though, but it doesn't make sense to tell the database that you want a distinct value from one column, and a value from another when the other might have multiple values. The database doesn't know which value to get, so it returns both of them.

yeah you are right thank you for your help,It works now, i used min(priority) and group by title...thanks a lot