CODEWITHSUNDEEP
mysqllimitdistinct
user1881190
user1881190

Reputation:

Limit distinct values in MySQL

I'm building something extremely similar to a "store locator" app. This queries by lat and lon and produces the nearest locations within a radius. This is working perfectly fine, but my database includes locations and sub-locations. I would like to be able to limit the distinct results the users get by location, while still querying and obtaining all information about the sub-location..

Say this is my result set before the distinct selection is applied:

| Location | Sub-Location |
+----------+--------------+
| Alpha    | Alpha North  |
| Alpha    | Alpha East   |
| Alpha    | Alpha West   |
| Beta     | Beta West    |
| Gamma    | Gamma North  |
| Gamma    | Gamma South  |
| Delta    | Delta West   |
| Delta    | Delta West 2 |

I need a way to can specify a range - let's say 2 - and produce the following result set:

| Location | Sub-Location |
+----------+--------------+
| Alpha    | Alpha North  |
| Alpha    | Alpha East   |
| Alpha    | Alpha West   |
| Beta     | Beta West    |

This would be the effective equivalent of producing the "two nearest" locations to the user. After a length of Googling and scouring Stack Overflow's suggestions and similar questions, I can't find anything that fits this description.

Would someone mind pointing me in the right direction, or perhaps provide a query example that can do this?

EDIT: This is the query I'm running with. It's a bit of a monster; includes the lat/lon query as well.

SELECT * 
FROM( 
(SELECT * FROM locationstable JOIN
(SELECT DISTINCT location, 
( 3959 * acos( cos( radians(47.4972680) ) * cos( radians( lat ) ) 
* cos( radians( lon ) - radians(-122.2564740) ) + sin( radians( 47.4972680) ) 
* sin( radians( lat ) ) ) ) AS distance 
FROM locationstable
HAVING distance < 1
LIMIT 0, 5)
locationstable ON locationstable.location = location
)) locationstable 
LEFT JOIN informations 
ON substring(locationstable.locationsublocation, 4) = informations.storeinformations

Upvotes: 9

Views: 10766

Answers (1)

parnas
parnas

Reputation: 721

How about subquery? 

SELECT * FROM sometable WHERE Location IN (SELECT DISTINCT Location FROM sometable LIMIT 2);

Upvotes: 9

Related Questions