从MySQL表中过滤重复的行

Question

My immediate apologies if this has been answered elsewhere. I can't seem to get a working version of this, and I have tried many different things, some from knowledge and others from googling around a bit.

I am using the Haversine formula in my SQL query to order the distance of a few retail stores so I can plot them on a map. I am getting the distances and what not fine, but when I added an option to filter out duplicate, or chain, stores I run into problems. The problems differ based off the different queries I've tried, and I was wondering if someone could point out where I'm going wrong so I can learn from this experience. :D

Things I have tried (note: the lat/lon in these examples are false lat/lon and the lat/lon I get is from another SQL table):

SELECT store_id,col_a,col_b,col_c,store_name,store_number,street_address,apt_suite,city,state_id,zip_code,latitude,longitude,phone_number,phone_extension,fax_number,email_addr,location_direction,open_24_hr,website_url, (3959*acos(cos(radians(12.1234567)) * cos(radians(latitude)) * cos(radians(longitude)-radians(-45.678910.)) + sin(radians(12.1234567)) * sin(radians(latitude)))) AS distance FROM stores WHERE primary_provider_code = '01' OR secondary_provider_code = '01' OR tertiary_provider_code = '01' ORDER BY distance LIMIT 0 , 10

This will get me the basics done. Gets nearest 10 stores, orders by distance, and only selecting the columns I want. Now when someone clicks the "filter same chain" option, I've tried the following:

SELECT store_id,col_a,col_b,col_c,store_name,store_number,street_address,apt_suite,city,state_id,zip_code,latitude,longitude,phone_number,phone_extension,fax_number,email_addr,location_direction,open_24_hr,website_url, (3959*acos(cos(radians(12.1234567)) * cos(radians(latitude)) * cos(radians(longitude)-radians(-45.678910.)) + sin(radians(12.1234567)) * sin(radians(latitude)))) AS distance FROM stores WHERE primary_provider_code = '01' OR secondary_provider_code = '01' OR tertiary_provider_code = '01' GROUP BY store_name ORDER BY distance LIMIT 0 , 10

This does render results, but it literally filters out every same-chain, not just all after one is shown. For example if we have Walgreens, Costco, Wal-Mart, Walgreens, Target, etc... then I only want to show the first Walgreens and filter the second one out. Google searches led me to believe that the GROUP BY clause would work. It removed all Walgreens. In fact, it removed all rows that had duplicates.

I have also tried GROUP BY store_name HAVING COUNT(*) = 1 and >,<,>=,<= variations for the sake of trying.

I don't believe I can do a SELECT DISTINCT since the the other columns are all going to be distinct in themselves, so even if the store_name may have duplicates, all the other rows I'm trying to select are unique. Also, I have tried this and confirmed it does not filter out non unique columns.

Thanks in advance for the help. Note that I've tagged PHP in as well since it is known to interface with SQL and the platform I'm developing on is PHP.

NOTE: I'm not looking for only one store. I am looking for all stores, ordered by distance, grouped and filtering out the duplicate stores. Let's say the unfiltered result is

Wal-Mart
K-Mart
Wal-Mart
Walgreens
Costco
Sams Club
Wal-Mart
Costco
Walgreens

I want to return the filtered result of:

Wal-Mart
K-Mart
Walgreens
Costco
Sams Club

Answer 1

One solution:-

SELECT store_id,col_a,col_b,col_c,stores.store_name,store_number,street_address,apt_suite,city,state_id,zip_code,latitude,longitude,phone_number,phone_extension,fax_number,email_addr,location_direction,open_24_hr,website_url, sub1.distance AS distance 
FROM stores 
INNER JOIN
(
    SELECT store_name, MIN(3959*acos(cos(radians(12.1234567)) * cos(radians(latitude)) * cos(radians(longitude)-radians(-45.678910.)) + sin(radians(12.1234567)) * sin(radians(latitude)))) AS distance 
    FROM stores 
    WHERE primary_provider_code = '01' OR secondary_provider_code = '01' OR tertiary_provider_code = '01' 
    GROUP BY store_name 
) sub1
ON stores.store_name = sub1.store_name
AND (3959*acos(cos(radians(12.1234567)) * cos(radians(latitude)) * cos(radians(longitude)-radians(-45.678910.)) + sin(radians(12.1234567)) * sin(radians(latitude)))) = sub1.distance
WHERE primary_provider_code = '01' OR secondary_provider_code = '01' OR tertiary_provider_code = '01' 
ORDER BY distance LIMIT 0 , 10

This uses a sub query to get the nearest store for each chain (hence the MIN / GROUP BY in the sub query), then joins that against the stores table to get the full details of the nearest store.