Posted by David Portas on 02/27/06 16:46

@sh wrote:
> This may be a really simple question, but I always have problems with
> Distinct queries.
>
> In this instance, I have a table of Delivery addresses, some will be exactly
> the same EXCEPT for the DeliveryID field, but eitherway I need the
> DeliveryID so that I can pass it onto the next form.
>
> So, I need to run a query that will find only addresses that are unique
> whilst retaining the respective DID for the rows...
>
> SELECT DISTINCT DeliveryID, Address, Organisation, Organisation, Town,
> County, PostCode FROM SOL_Delivery WHERE CustomerID = 15)
>
> How do I adjust the following to only 'Distinct' the fields other than the
> DeliveryID?
>
> Thanks!

Given that there may be more than one ID per address, which ID do you
want to see in the result? To get the minimum ID value for example:

SELECT MIN(deliveryid), address, organisation, town, county, postcode
FROM sol_delivery
WHERE customerid = 15
GROUP BY address, organisation, town, county, postcode ;

Why not get rid of the duplicates and then add a unique constraint so
that you prevent them in future?

For future reference remember that SELECT DISTINCT always applies the
distinct operator across ALL columns in the result. That's why GROUP BY
is what you actually require here.

--
David Portas, SQL Server MVP

Whenever possible please post enough code to reproduce your problem.
Including CREATE TABLE and INSERT statements usually helps.
State what version of SQL Server you are using and specify the content
of any error messages.

SQL Server Books Online:
http://msdn2.microsoft.com/library/ms130214(en-US,SQL.90).aspx
--

[Back to original message]