I've got a query that selects the nearest 10 addresses to a lat/long.
I need to join that with another query that selects the nearest 2 enhanced addresses to the same lat/long.
Then limit the results to 10 showing the enhanced listings at the top.
The 2 enhanced centres might be in the original 10 or might be outside, so the results could be 10, 11 or 12.
Is there an equivalent to "select top" in mysql or is there an alternative solution?
If TOP was available, here's what I would do:
/* Top 10 nearest */
SELECT *TOP 10* sit.itemid, sit.title, sit.owner, sit.image, sit.background, sit.icon, sit.metadesc, sit.publish_up,
"" as category_name, 0 as category,
"data" as data,
"geom" as geom,
MAX(IF(fd.fieldid=1,fd.data_txt, NULL)) as "street_field",
MAX(IF(fd.fieldid=3,fd.data_txt, NULL)) as "city_field",
MAX(IF(fd.fieldid=5,fd.data_txt, NULL)) as "state_field",
MAX(IF(fd.fieldid=2,fd.data_txt, NULL)) as "zip_field",
lat.data_txt as "lat",
lng.data_txt as "lng",
member.data_txt as "member",
( 3959 * acos( cos( radians(52.375201) ) * cos( radians( lat.data_txt) ) * cos( radians( lng.data_txt) - radians(-2.316263) ) + sin( radians(52.375201) ) * sin( radians( lat.data_txt) ) ) ) as distance
FROM jos_sobi2_fields_data as member, jos_sobi2_fields_data as lat, jos_sobi2_fields_data as lng,
jos_sobi2_item as sit
JOIN jos_sobi2_fields_data as fd
ON sit.itemid = fd.itemid
WHERE fd.fieldid IN(1,2,3,5)
AND sit.itemid = lat.itemid AND lat.fieldid=15
AND sit.itemid = lng.itemid AND lng.fieldid=14
AND sit.itemid = member.itemid AND member.fieldid=93
AND sit.published > 0
AND sit.approved > 0
AND sit.publish_up > sit.publish_down
GROUP BY sit.itemid
UNION
/* top 2 nearest enhanced members */
SELECT *TOP 2* sit.itemid, sit.title, sit.owner, sit.image, sit.background, sit.icon, sit.metadesc, sit.publish_up,
"" as category_name, 0 as category,
"data" as data,
"geom" as geom,
MAX(IF(fd.fieldid=1,fd.data_txt, NULL)) as "street_field",
MAX(IF(fd.fieldid=3,fd.data_txt, NULL)) as "city_field",
MAX(IF(fd.fieldid=5,fd.data_txt, NULL)) as "state_field",
MAX(IF(fd.fieldid=2,fd.data_txt, NULL)) as "zip_field",
lat.data_txt as "lat",
lng.data_txt as "lng",
member.data_txt as "member",
( 3959 * acos( cos( radians(52.375201) ) * cos( radians( lat.data_txt) ) * cos( radians( lng.data_txt) - radians(-2.316263) ) + sin( radians(52.375201) ) * sin( radians( lat.data_txt) ) ) ) as distance
FROM jos_sobi2_fields_data as member, jos_sobi2_fields_data as lat, jos_sobi2_fields_data as lng,
jos_sobi2_item as sit
JOIN jos_sobi2_fields_data as fd
ON sit.itemid = fd.itemid
WHERE fd.fieldid IN(1,2,3,5)
AND sit.itemid = lat.itemid AND lat.fieldid=15
AND sit.itemid = lng.itemid AND lng.fieldid=14
AND sit.itemid = member.itemid AND member.fieldid=93 AND member.data_txt="1"
AND sit.published > 0
AND sit.approved > 0
AND sit.publish_up > sit.publish_down
GROUP BY sit.itemid
ORDER BY member DESC, distance
LIMIT 10