MySQL Forums :: Newbie :: select top x with a union


Advanced Search

select top x with a union
Posted by: Russell England ()
Date: October 06, 2010 02:52AM

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

Options: ReplyQuote


Subject Written By Posted
select top x with a union Russell England 10/06/2010 02:52AM
Re: select top x with a union Russell England 10/06/2010 03:32AM
Re: select top x with a union Rick James 10/10/2010 12:31PM


Sorry, you can't reply to this topic. It has been closed.

Content reproduced on this site is the property of the respective copyright holders. It is not reviewed in advance by Oracle and does not necessarily represent the opinion of Oracle or any other party.