...

View Full Version : SQL on MS SQL Server using DISTINCT clause



bcarl314
10-22-2003, 06:13 PM
I'm migrating a program from PHP/mySQL to PHP/SQL Server (not my idea) and am having some minor problems with the SQL.

I've got a table (results) set up as follows...
questionID int not null,
resultText varchar(255) not null,
lastUpdated datetime

Now, on mySQL I ran this query...

SELECT DISTINCT resultText FROM results ORDER BY lastUpdated

which worked great.

On SQL Server, appearenlty you need to include the filed which you want to order by in the select statement.

IE my new query is...

SELECT DISTINCT resultText, lastUpdated FROM results ORDER BY lastUpdated.

Since lastUpdated is almost always unique, I get the entire table returned.

All I want is the unique results from the resutls table ordered by the lastUpdated column. Any ideas?

Roy Sinclair
10-22-2003, 10:22 PM
Try:

select distinct resulttext from (select resulttext,lastupdated from results order by lastupdated)

I've got a similar problem on a much more complex query in a program I'm working on right now so this is a familiar problem :( .

bcarl314
10-23-2003, 02:13 PM
Thanks, that led me in the right direction.

Here's the query that worked, hopefully it will help you out with your problem as well...

SELECT DISTINCT resultText FROM (SELECT TOP 50000000 resultText, lastUpdated FROM s_results_text ORDER BY lastUpdated) DERIVEDTBL

Good luck



EZ Archive Ads Plugin for vBulletin Copyright 2006 Computer Help Forum