sql server full text search: Using DISTINCT and ORDER BY in the same query -- sql server full text search

Re: Using DISTINCT and ORDER BY in the same query

Dan Guzman
7/26/2007 7:49:14 PM

sql server full text search: [quoted text, click to view]

Since you have many field 3 values per distinct field1, which those field3
values do you want to order the result by? Perhaps an aggregate query is in
order (no pun intended) instead of DISTINCT. Try something like:

SELECT field1
FROM dbo.table1
GROUP BY field1
ORDER BY MIN(field3)

--
Hope this helps.

Dan Guzman
SQL Server MVP

[quoted text, click to view]

Using DISTINCT and ORDER BY in the same query

Nathan Sokalski
7/26/2007 8:08:15 PM

I have the following SQL statement:

SELECT field1 FROM table1 WHERE field2=25 ORDER BY field3

This statement works fine, but the results contain duplicates, because
field1 contains the same value in multiple records. I would have thought
that modifying the statement with the keyword DISTINCT to look like the
following would fix this for me:

SELECT DISTINCT field1 FROM table1 WHERE field2=25 ORDER BY field3

However, this gives me an error saying that the field used for ORDER BY must
be included in the SELECT list. However, if I do the following like the
error says I need to:

SELECT DISTINCT field1,field3 FROM table1 WHERE field2=25 ORDER BY field3

Then using DISTINCT does not help me, because I end up with results like the
following:

1 1
1 2
1 3
1 4
2 1
2 2
2 3
2 4

As you can see, the rows are still DISTINCT, but the field that I care about
(field1, the first column here) is not DISTINCT. If I remove the ORDER BY
clause from my original statement, I can add the DISTINCT keyword with no
problem, but then my results are not sorted. Is there a way to use DISTINCT
and ORDER BY together without returning any undesired fields in the results?
I would think their would be, because I have had plenty of situations in
which I would want it, and in multiple cases I have been forced to write
extra code in my ASP.NET codebehind to workaround this inconvenience, and I
doubt that I'm the only person who has been in this scenario. (NOTE: In this
specific case, I was using SQL Server 2005, but I have been in the same
scenario with other databases as well). Thanks.
--
Nathan Sokalski
njsokalski@hotmail.com
http://www.nathansokalski.com/

Got something to say? Click here to post a reply to this thread.

Don't see what you're looking for? Try a search.

View other messages in the sql server full text search group.