Simple SQL Distinct...!?

[@sh]

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!

[markc600]

For the non-unique addresses, you will have a choice
of DeliveryIDs, which one do you want?

If you want just one of them, you can do this

SELECT MAX(DeliveryID), Address, Organisation, Organisation, Town,
County, PostCode
FROM SOL_Delivery
WHERE CustomerID = 15
GROUP BY Address, Organisation, Organisation, Town,
County, PostCode

[David Portas]

@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/m...S,SQL.90).aspx
--

[@sh]

Thanks to you both for your replies, I did actually try the Group By but
received the 'Aggregate function' error message that I couldn't quite
interpret into English....!!!

Many thanks, I'll give those a go - in the meantime I actually used this in
the end...

SELECT DeliveryID, Address, Organisation, Organisation, Town, County,
PostCode FROM SOL_Delivery WHERE DeliveryID IN (SELECT DISTINCT
REPLACE(Postcode,' ','') FROM sol_delivery WHERE CID = CID (ASP Variable)
ORDER BY Address DESC

....but I'll replace with your variants if you think the above is less
efficient?

Cheers, Ash

[Tom Moreau]

Could you please post sample data and expected results?

--
Tom

----------------------------------------------------
Thomas A. Moreau, BSc, PhD, MCSE, MCDBA
SQL Server MVP
Columnist, SQL Server Professional
Toronto, ON Canada
www.pinpub.com
..
"@sh" <sp**@spam.com> wrote in message
news:dt**********@nwrdmz01.dmz.ncs.ea.ibs-infra.bt.com...
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!

[@sh]

UPDATE...

I'll definitely be using your suggestions seen as mine didn't work, hehe -
seemed like a good idea at the time, not sure what I thinking looking back
at it now...

Cheers, Ash
"@sh" <sp**@spam.com> wrote in message
news:dt**********@nwrdmz02.dmz.ncs.ea.ibs-infra.bt.com...

Thanks to you both for your replies, I did actually try the Group By but
received the 'Aggregate function' error message that I couldn't quite
interpret into English....!!!

Many thanks, I'll give those a go - in the meantime I actually used this
in the end...

SELECT DeliveryID, Address, Organisation, Organisation, Town, County,
PostCode FROM SOL_Delivery WHERE DeliveryID IN (SELECT DISTINCT
REPLACE(Postcode,' ','') FROM sol_delivery WHERE CID = CID (ASP Variable)
ORDER BY Address DESC

...but I'll replace with your variants if you think the above is less
efficient?

Cheers, Ash

[@sh]

Its basically an application where they've previously had lots of
opportunities to enter delivery address and so there's duplicates as they
enter a new one each time.

I'm adding a page that looks up all 'unique' Delivery Addresses (although
each has a unique DID, DeliveryID), and I want to just show the latest
unique addresses that have the highest DID

So instead of getting this...

, ### Waterleaze, Maidenbrook, Taunton, Somerset, Somerset, TA7 8PS
Test, Ston Easton, Bath, Bath, BA3 4DF
, ### Waterleaze, Maidenbrook, Taunton, Somerset, Somerset, TA7 8PS
, ### Waterleaze, Maidenbrook, Taunton, Somerset, Somerset, TA7 8PS
Test, Ston Easton, Bath, Bath, BA3 4DF
, ### Waterleaze, Maidenbrook, Taunton, Somerset, Somerset, TA7 8PS
You would get this...

, 120 Waterleaze, Maidenbrook, Taunton, Somerset, Somerset, TA7 8PS
Test, Ston Easton, Bath, Bath, BA8 4DF
The DID becomes a hidden value applicable to the radio button for that
address.

Does that make sense? The trouble with the two other suggestions is that the
DID column value doesn't actually become a usable field in the recordset
that I create in ASP.

Cheers, Ash
<ma******@hotmail.com> wrote in message
news:11**********************@v46g2000cwv.googlegr oups.com...

For the non-unique addresses, you will have a choice
of DeliveryIDs, which one do you want?

If you want just one of them, you can do this

SELECT MAX(DeliveryID), Address, Organisation, Organisation, Town,
County, PostCode
FROM SOL_Delivery
WHERE CustomerID = 15
GROUP BY Address, Organisation, Organisation, Town,
County, PostCode

[markc600]

As David has already mentioned, you would be
better off fixing this to prevent duplicates
appearing in the first place. Picking the
latest address using the highest DeliveryID
will probably work (assuming this is an identity),
but you are filling your table with redundant
data - not good in the long term.

[@sh]

I totally agree and by offering them the latest address that matches the
address to which they're sending, a new one wouldn't be inserted, I'd pass
that DID (DeliveryID) on for use with order.

Is there a way to get the DID into the query too?

Cheers, Ash
<ma******@hotmail.com> wrote in message
news:11**********************@v46g2000cwv.googlegr oups.com...

As David has already mentioned, you would be
better off fixing this to prevent duplicates
appearing in the first place. Picking the
latest address using the highest DeliveryID
will probably work (assuming this is an identity),
but you are filling your table with redundant
data - not good in the long term.