Want to protect your cyber security and still get fast solutions? Ask a secure question today.Go Premium

x
  • Status: Solved
  • Priority: Medium
  • Security: Public
  • Views: 861
  • Last Modified:

Access select distinct rows based only on certain fields

I have a mailing list with 100 rows.
Each row represents a catalog request.
There are 5 difference catalogs.
If a user requests 3 catalogs, he appears in 3 rows.
If a user requests only 1 catalog, he only appears one time.

I am trying to write a query that returns the unique addresses within the table.
So in other words... I don't care if a customer requested 1 catalog or 3 catalogs or 5 catalogs... I only want to see his address one time in the query result.

What makes an address unique? Concatenate 5 fields: Name + Address + City + State + Zip. Call that 'MailingAddress', then that is what needs to be unique. This ensures that if two people at the same address requested a catalog, they would both remain on the mailing list.


Sample data:
   John Smith - 123 Main Street, Atlanta, GA - CatalogA
   John Smith - 123 Main Street, Atlanta, GA - CatalogB
   John Smith - 123 Main Street, Atlanta, GA - CatalogC
   Michelle Michaels - 4226 Hollywood Blvd, Hollywood, CA - CatalogE
   Charlotte Nieves - 426 NW 20th Avenue, Seattle, WA - CatalogA
   Charlotte Nieves - 426 NW 20th Avenue, Seattle, WA - CatalogB
   Michelle Michaels - 4226 Hollywood Blvd, Hollywood, CA - CatalogC
   John Smith - 123 Main Street, Atlanta, GA - CatalogA

So the results of the query should give me:
   John Smith - 123 Main Street, Atlanta, GA
   Michelle Michaels - 4226 Hollywood Blvd, Hollywood, CA
   Charlotte Nieves - 426 NW 20th Avenue, Seattle, WA
0
ecarbone
Asked:
ecarbone
  • 2
  • 2
  • 2
2 Solutions
 
Rey Obrero (Capricorn1)Commented:
drop the field for the catalog from your query and use a total query ( group by )

select [Name], Address
from tableName
group by [Name], address
0
 
PortletPaulCommented:
"select distinct" is utterly unconditional
i.e. "select distinct" considers the whole row (so if you have catalog in the row it is being considered).

so "select distinct rows based only on certain fields"
means you cannot use select distinct on ALL columns (to meet the wanted conditions).

select distinct
   Name, Address ,City, State, Zip
from YourTable

OR

select
   Name, Address ,City, State, Zip
from YourTable
group by
   Name, Address ,City, State, Zip

and, an advantage of using group by would be something like this:

select
   Name, Address ,City, State, Zip, count(distinct catalog)
from YourTable
group by
   Name, Address ,City, State, Zip
having count(distinct catalog) > 0

NB: this is not access syntax (sorry, not an Access user) but hopefully it's useful
0
 
ecarboneAuthor Commented:
Hi Paul,
Thank you. A few questions:

1.
So, I can use either one ('select distinct' or 'group by') AS LONG AS I leave out the fields that are not considered part of the 'de-duplication' criteria. Is that correct?

2.
Seems like 'select distinct' is more concise and gives me a quick result, whereas the 'group by' method gives me the same result but also allows me to use some sort of calculation (such as 'count' or 'sum'). Is that correct?
0
NFR key for Veeam Agent for Linux

Veeam is happy to provide a free NFR license for one year.  It allows for the non‑production use and valid for five workstations and two servers. Veeam Agent for Linux is a simple backup tool for your Linux installations, both on‑premises and in the public cloud.

 
Rey Obrero (Capricorn1)Commented:
yes to both 1 and 2

if you want to display all the catalogs in one field, that can also be done by using a user define function in your query

i.e.,
Name             Address                                    Catalogs
John Smith - 123 Main Street, Atlanta, GA  CatalogA; CatalogB; CatalogC
0
 
ecarboneAuthor Commented:
Thank you both. This is exactly what I was looking for.
0
 
PortletPaulCommented:
1 correct

2. Don't underestimate the advantages of 'group by'; but you are also correct here - 'select distinct' can be very convenient.

Just remember that "the whole row" is compared by select distinct so if there are lots of big text fields the query sql may be brief but the query execution not brief
0

Featured Post

VIDEO: THE CONCERTO CLOUD FOR HEALTHCARE

Modern healthcare requires a modern cloud. View this brief video to understand how the Concerto Cloud for Healthcare can help your organization.

  • 2
  • 2
  • 2
Tackle projects and never again get stuck behind a technical roadblock.
Join Now