Irtza Shahan Irtza Shahan - 3 months ago 15
MySQL Question

Use Order By, and Group by on a Union result from 2 different columns

SELECT `SenderID` FROM `messages` WHERE `RecieverID` = 2
UNION
SELECT `ReceiverID` FROM `messages` WHERE `SenderID` = 2


This is my query and gives correct answer but not correctly sorted I want to order by ID, for which I have to select ID in both clauses as well which makes the rows unique and give me duplicate ID from senderid and receiverID table
for trying to remove duplication I also Used the following

SELECT ID, `SenderID` FROM `messages` WHERE `RecieverID` = 2
UNION
SELECT ID, `RecieverID` FROM `messages` WHERE `SenderID` = 2
GROUP BY (SenderID)
order by ID ASC


but it gives me a duplicated ID from each sender and receiver id column and also for some odd reason leaves out a distinct ID as well
Any Suggestions please.

Answer

you need to use inner query in a brackets in order to set what the order by refers to:

SELECT id, senderID from 
  (SELECT ID, `SenderID` FROM `messages` WHERE `RecieverID` = 2
   UNION
   SELECT ID, `RecieverID` as senderId FROM `messages` WHERE `SenderID` = 2
  ) as A
GROUP BY (SenderID)
order by ID ASC