How to apply ORDER BY to the UNION SQL Query

Posted on March 25, 2012, 5:29 pm

---

How to apply ORDER BY to the UNION SQL Query ?

ដំរីជើងបួនគង់តែ... អ្នកប្រាជ្ញគង់តែ... ពាក្យចាស់ពាក្យខ្មែពីព្រេង មិនដែរខុស សប្តាហ៍នេះ ជួបជាមួយ Directeur Technique…
គាត់ មើល SQL Query code ខ្ញុំហើយ គាត់ប្រាប់ ក្បាច់ទី ២ មកដល់កន្លែង វិញ សរសេបាន ១ក្បាច់ទៀត សរុប ៣ (ក្បាច់) គាត់ក៏ភ្លេ ខ្ញុំក៏មិនចាំ :(
ស្រួសសោះ មិនគួរណា គិតអីក៏ស្មុកស្មាញមេ្លះ ប្រើ UNION រវៀង ២ Query ហើយ យកវាធ្វើជា Sub-Query ទៀត ;(
USE ORDER BY in the UNION SQL Query.

1st Style: (ប្រើរង្វង់ក្រច អម Query ទាំងពីរ ហើយ ប្រើ Order By តែមួយ, គ្រប់ Field នៃ Query ទាំងពីរត្រូវតែដូចគ្នា)

(
SELECT t.`temoignage_id`, t.`prospect_id`,t.`temoignage_date_ajout`,t.`temoignage_texte`,t.`temoignage_miseenavant`,
CONCAT(t.`temoignage_prenom`, ' ', SUBSTRING(t.`temoignage_nom` FROM 1 FOR 1),'.') AS nom
FROM `temoignage` AS t WHERE t.`prospect_id` = 0 AND t.`temoignage_type_prospect` = 'un bricoleur'
)
UNION
(
SELECT t.`temoignage_id`, t.`prospect_id`, t.`temoignage_date_ajout`, t.`temoignage_texte`,t.`temoignage_miseenavant`,
CONCAT(p.`prospect_prenom`, ' ', SUBSTRING(p.`prospect_nom` FROM 1 FOR 1),'.') AS nom
FROM `temoignage` AS t
LEFT JOIN `prospect` AS p ON t.`prospect_id` = p.`prospect_id`
WHERE t.`prospect_id` > 0 AND t.`temoignage_type_prospect` = 'un bricoleur'
)
ORDER BY `temoignage_miseenavant` ASC, `temoignage_date_ajout` DESC

2nd Stye (this is how i use, how i complicated my life ;( why complicated my life ? ប្រើ sub query ទើបប្រើ Order By)

SELECT tp.* FROM(
  SELECT t.`temoignage_id`,   t.`prospect_id`,t.`temoignage_date_ajout`,t.`temoignage_texte`,t.`temoignage_miseenavant`,
  CONCAT(t.`temoignage_prenom`, ' ', SUBSTRING(t.`temoignage_nom` FROM 1 FOR 1),'.') AS nom
  FROM `temoignage` AS t WHERE t.`prospect_id` = 0 AND t.`temoignage_type_prospect` = 'un bricoleur'
UNION
  SELECT t.`temoignage_id`, t.`prospect_id`, t.`temoignage_date_ajout`,   t.`temoignage_texte`,t.`temoignage_miseenavant`,
  CONCAT(p.`prospect_prenom`, ' ', SUBSTRING(p.`prospect_nom` FROM 1 FOR 1),'.') AS nom
  FROM `temoignage` AS t
  LEFT JOIN `prospect` AS p ON t.`prospect_id` = p.`prospect_id`
  WHERE t.`prospect_id` > 0 AND t.`temoignage_type_prospect` = 'un bricoleur'
) AS tp
ORDER BY tp.`temoignage_miseenavant` ASC, tp.`temoignage_date_ajout` DESC

3rd Style (This on not use UNION, អាក្បាចនេះស្រួល ហើយខ្លីទៀត )

SELECT t.`temoignage_id`,t.`prospect_id`,t.`temoignage_date_ajout`,t.`temoignage_texte`,t.`temoignage_miseenavant`,
IF(t.`prospect_id`=0,
CONCAT(t.`temoignage_prenom`, ' ', SUBSTRING(t.`temoignage_nom` FROM 1 FOR 1),'.'),
CONCAT(p.`prospect_prenom`, ' ', SUBSTRING(p.`prospect_nom` FROM 1 FOR 1),'.')
)
AS nom
FROM `temoignage` AS t
LEFT JOIN `prospect` AS p ON t.`prospect_id` = p.`prospect_id`
WHERE t.`temoignage_type_prospect` = 'un bricoleur'
ORDER BY t.`temoignage_miseenavant` ASC, t.`temoignage_date_ajout` DESC