sights

sights.sql

SELECT
               `sights_lang`.`name`,
               `sights_lang`.`address`,
               `sights`.`category_id`,
               `sights_categories_lang`.`
categoryName`,
               `sights`.`latitude`,
               `sights`.`longitude`,
               `sights`.`sights_id`
           FROM               `partner_hotels`.`sights_lang`
           JOIN               `partner_hotels`.`sights`
           ON               (`sights_lang`.`sights_id` = `sights`.`sights_id`)
           JOIN               `partner_hotels`.`sights_categories_lang`
           ON               (`sights_categories_lang`.`category_id` = `sights`.`category_id`)
           WHERE   sights.sights_id
           IN (
               SELECT
                   sights_id FROM (
                       SELECT
                           sights.sights_id,
                           @rn := CASE WHEN @category_id=category_id
THEN @rn + 1 ELSE 1 END AS rn,
                           @category_id := category_id
                       FROM
                           partner_hotels.sights,
                           (SELECT @rn := 0, @category_id := NULL) AS vars
                       WHERE                            (latitude BETWEEN 20 AND 70)
                       AND                            (longitude BETWEEN 30 AND 70)
                       AND                            `category_id` IN(1, 2, 3, 4, 5, 27, 28,
29, 31, 40, 52, 53, 55, 57, 79, 80, 81, 82)

               ORDER BY                            category_id,
POW(45-latitude,2)+POW(48-longitude,2) ASC
                   )
               AS                    T1
               WHERE                    rn <= 10
           )
           ORDER BY 3, POW(45-latitude,2)+POW(48-longitude,2) ASC