Simple SQL Distinct...!?

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!

re: Simple SQL Distinct...!?

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

re: Simple SQL Distinct...!?

@sh wrote:[color=blue]
> 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![/color]

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
--

re: Simple SQL Distinct...!?

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

re: Simple SQL Distinct...!?

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" <spam@spam.com> wrote in message
news:dtv2c2$mhe$1@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!

re: Simple SQL Distinct...!?

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" <spam@spam.com> wrote in message
news:dtv3r4$at1$1@nwrdmz02.dmz.ncs.ea.ibs-infra.bt.com...[color=blue]
> 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
>[/color]

re: Simple SQL Distinct...!?

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


<markc600@hotmail.com> wrote in message
news:1141051443.398579.176470@v46g2000cwv.googlegr oups.com...[color=blue]
> 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
>[/color]

re: Simple SQL Distinct...!?

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.

re: Simple SQL Distinct...!?

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


<markc600@hotmail.com> wrote in message
news:1141055631.655033.148880@v46g2000cwv.googlegr oups.com...[color=blue]
>
> 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.
>[/color]