SQL Distinct or Group By - how to Select * ???

[Pav1977]

I need to make all the records in the new ExportedSMAll distinct – (as each entrance for the same story should have different category2).

I’m not able to make up a statement that lists all the entries needed.

What I want is a set of results as listed by any of the 2 queries below but with all of the columns:

select distinct ref, Category2
from ExportedSMAll
order by ref



SELECT ref, Category2
FROM ExportedSMAll
group by ref, Category2
order by ref

Another words something like this:

SELECT *
FROM ExportedSMAll
group by ref, Category2
order by ref

This of course has incorrect syntax though.


I many of the other fields are text so I can't do Distinct on the whole table (as it results in an error - saying that Distinct can't be run on Text fields)

The table is like this:
ref title dated author summary article Category2

It contains multiple copies or each article with many different categories (Category2) the problem is that Category2 needs to me unique - but it's not at the moment. There is no other unique field at the moment.


If I can't do it like that I'll do it on the application level but would rather avoid that.


I’d really appreciate your help.

Thanks

 

[gmmastros]

What version of SQL Server are you using?

If you're not sure...

Select @@Version

-George

"The great things about standards is that there are so many to choose from." - Fortune Cookie Wisdom

 

[Pav1977]

Microsoft SQL Server 2005 - 9.00.4035.00 (Intel X86) Nov 24 2008 13:01:59 Copyright (c) 1988-2005 Microsoft Corporation Standard Edition on Windows NT 5.2 (Build 3790: Service Pack 2)

Thanks

 

[gmmastros]

I many of the other fields are text so I can't do Distinct on the whole table (as it results in an error - saying that Distinct can't be run on Text fields)

Microsoft SQL Server 2005

Since you are using SQL Server 2005, I would encourage you to change the data type for your text column(s). If the original data type is text, then change it to varchar(max). If the original data type is ntext, then change it to nvarchar(max).

varchar(max) allows you to store the same amount of data as text. The difference is.... the string handling functions that do NOT work with text, do work with varchar(max).

Changing to varchar(max) will also allow your distinct to work.

-George

"The great things about standards is that there are so many to choose from." - Fortune Cookie Wisdom