CODEWITHSUNDEEP
mysqlsqlunion
SilentCoder
SilentCoder

Reputation: 33

mysql union give priority to record from one of the select queries in case of duplicates

This is my query in a real example :

     SELECT *
 FROM (

(SELECT Deal.DealID, Deal.TitleEn, Deal.CapNum,Deal.ImageLink,
     SUM( Buy.Quantity ) AS Quantity
 FROM Buy, Deal
 WHERE Buy.DealID = Deal.DealID
 AND Buy.IsFinalBuy =  '1'
 GROUP BY Buy.DealID
HAVING Quantity = Deal.CapNum)

union
(SELECT Deal.DealID, Deal.TitleEn, Deal.CapNum,Deal.ImageLink,NULL AS Quantity
 FROM Deal
 WHERE Deal.EndDate < CURDATE( ) 
)
  )A

  ORDER BY DealID,TitleEn,CapNum,ImageLink,Quantity ASC

I want to give priority to the record from the first select query in case of duplicates .

How can it be done?

Upvotes: 3

Views: 1512

Answers (1)

Conrad Frix
Conrad Frix

Reputation: 52645

Just add a literal value to each one and then order by it. 

SELECT * 
FROM   ((SELECT deal.dealid, 
                deal.titleen, 
                deal.capnum, 
                deal.imagelink, 
                SUM(buy.quantity) AS quantity, 
                'A'               sortby 
         FROM   buy, 
                deal 
         WHERE  buy.dealid = deal.dealid 
                AND buy.isfinalbuy = '1' 
         GROUP  BY buy.dealid 
         HAVING quantity = deal.capnum) 
        UNION 
        (SELECT deal.dealid, 
                deal.titleen, 
                deal.capnum, 
                deal.imagelink, 
                NULL AS quantity, 
                'B'  sortby 
         FROM   deal 
         WHERE  deal.enddate < Curdate()))a 
ORDER  BY dealid, 
          sortby, 
          titleen, 
          capnum, 
          imagelink, 
          quantity ASC

Upvotes: 1

Related Questions