MySQL views cannot have subquery in from, and don't maintain order. How can I create a view from this query?

I'm essentially trying to obtain a resultset with each employee's current title. I'd like to create a view from this for later use, but I find I'm being stumped, and likely missing a simple solution. Here's the query in question, and thanks in advance!

select * from 
(SELECT
appointment.employee_id,
title.`name` as title_name
FROM
appointment
INNER JOIN appointment_title ON appointment.id = appointment_title.appointment_id
I开发者_如何学GoNNER JOIN title ON appointment_title.title_id = title.id
order by appointment_title.effective_date DESC) tmp group by employee_id

---

Updated:

SELECT
    appointment.employee_id ,
    ( SELECT title.`name`
      FROM appointment_title
        INNER JOIN title
          ON appointment_title.title_id = title.id
      WHERE appointment.id = appointment_title.appointment_id
      ORDER BY appointment_title.effective_date DESC
      LIMIT 1
    ) AS title_name
FROM appointment
GROUP BY appointment.employee_id

---

Another option is to break up the query into two views. The first view will contain the derived table subquery, and the second will simply select from that one:

CREATE VIEW vwEmployee_Inner AS
SELECT
appointment.employee_id,
title.`name` as title_name
FROM
appointment
INNER JOIN appointment_title ON appointment.id = appointment_title.appointment_id
INNER JOIN title ON appointment_title.title_id = title.id
order by appointment_title.effective_date DESC

And then your original view becomes:

CREATE VIEW vwEmployee AS
SELECT * FROM vwEmployee_Inner GROUP BY employee_id