CODEWITHSUNDEEP
mysqlsqlsubquery
arjunurs
arjunurs

Reputation: 1182

Converting sub-query to join to create a view

I have a nested SQL query :

SELECT * 
FROM
(
    SELECT *
    FROM asset_status
    ORDER BY session_id DESC
) tmp
GROUP BY asset_id, workflow_element_id

I would like to create a view from this query but MySQL doesn't seem to allow subqueries in views. How can convert this to a join?

Upvotes: 3

Views: 1564

Answers (3)

user359040
user359040

Reputation:

According to the MySQL reference manual, you can create views that use sub-queries, but not in the From clause.

Therefore, I think you need to create your view like the following:

select a.* 
from asset_status a
join (select asset_id, workflow_element_id, MAX(session_id) session_id
      from asset_status
      group by asset_id, workflow_element_id) sq
  on a.session_id = sq.session_id

However, it probably won't perform as well as your original query.

Upvotes: 0

Magnus
Magnus

Reputation: 46929

There is no need for subquery since the inner order by is not guaranteed to be used at all. You can write:

SELECT DISTINCT asset_id, workflow_element_id
FROM asset_status

If you need to order by session_id you would have to include it in an aggregate, max for example. (or in the group by)

SELECT asset_id, workflow_element_id
FROM asset_status
GROUP BY asset_id, workflow_element_id
ORDER BY MAX(session_id) DESC

Upvotes: 0

MatBailie
MatBailie

Reputation: 86706

SQL Server does allow sub-queries in views. What you can't do, is SELECT * and GROUP BY a, b

Have you tried... (I'll assume this isn't your whole query so I'll make the minimum possible changes)

SELECT asset_id, workflow_element_id
FROM
(
    SELECT *
    FROM asset_status
    -- ORDER BY session_id DESC   (Removed as innefective in a view)
) tmp
GROUP BY asset_id, workflow_element_id


Also, note that the ORDER BY in the inner query is innefective (and possibly even dis-allowed), as the outer query is then allowed to re-order it (it won't always come back in a different order, but this layout doesn't guarnatee the order you seem to want). Even in the outer query, it may cause your results to be order when using the view, but again the optimiser is allowed to re-order the results. Unless the ORDER BY is in the query using the view, the order is never absolutely guaranteed...

SELECT * FROM view ORDER BY x


Finally, you tagged this as a LEFT JOIN question. If you have a more complete example of the code, I'm sure someone will suggest an alternative layout. But I'm off out for a few days now. Good luck! :)

Upvotes: 2

Related Questions