CODEWITHSUNDEEP
mysql
Jay
Jay

Reputation: 193

How do I order by within a left join?

There are a lot of instances where people would need to order by within a left join, i am currently attempted to find the most recent job listing for example from a set of users, unfortunately, I am unable to get it to run, what i currently have is:

select * from `Profiles`
left join (select  isPrimary, startDate, Orgs.`orgName`, Orgs.`title` from        
Orgs order by isPrimary ) job  on 
`Profiles`.`ID` = Orgs.`ID` 
group by `Profiles`.`ID`
 limit 0,2

I feel i am likely close here, but can anyone offer some insight?

Upvotes: 0

Views: 67

Answers (3)

Abhijit Buchake
Abhijit Buchake

Reputation: 142

Join does not see what order the data is stored. In your query, the order in which data is displayed is determined by GROUP BY clause (which implicitly orders the data unless you mention ORDER BY).

Following query may help. Let me know if otherwise.

select * from `Profiles`
left join Orgs job  on `Profiles`.`ID` = Orgs.`ID` 
group by `Profiles`.`ID`
by order by job.isPrimary  
limit 0,2

Upvotes: 1

HLGEM
HLGEM

Reputation: 96552

Since mysql doesn't have ranking functions, you have to use the old traditional way. In this you create a derived table that aggregates the data you want and then join to the whole table to grab the other fields.

SELECT p.[define fields here, never use select * in production code], o3.isPrimary, o3.startDate, o3.orgname, o3.title 
FROM `Profiles` AS p
LEFT JOIN 
    (SELECT O.Id, o.isPrimary, o.startDate, o.orgname, o.title 
    FROM  `Orgs` as o
    JOIN (SELECT ID, MAX(Startdate) as Startdate FROM   `Orgs` GROUP BY ID) AS o2
      ON o.ID = o2.id and o.startdate = o2.startdate) as 03
ORDER BY p.ID, o3.isPrimary

I'm not thinking you need the outergroup by or the limit clause, but I'd have to see the results set to be sure. The biggest problem is if you might have more than one record with the same id and startdate in which case you would need to aggregate on another field as well to get uniqueness.

Upvotes: 1

Mike Brant
Mike Brant

Reputation: 71384

Why use the subselect here? Why not just:

SELECT p.*, o.isPrimary, o.startDate, o.orgname, o.title FROM `Profiles` AS p
LEFT JOIN `Orgs` AS o
  ON p.ID = o.ID    
GROUP BY p.ID
ORDER BY p.ID, o.isPrimary
LIMIT 0,2

Upvotes: 2

Related Questions