CODEWITHSUNDEEP
mysqlviewsubquery
Ryan Kempt
Ryan Kempt

Reputation: 4209

MySQL Query in a View

I have the following query I use and it works great:

SELECT * FROM 

(
SELECT * FROM `Transactions` ORDER BY DATE DESC
) AS tmpTable

GROUP BY Machine
ORDER BY Machine ASC

What's not great, is when I try to create a view from it. It says that subqueries cannot be used in a view, which is fine - I've searched here and on Google and most people say to break this down into multiple views. Ok.

I created a view that orders by date, and then a view that just uses that view to group by and order by machines - the results however, are not the same. It seems to have taken the date ordering and thrown it out the window.

Any and all help will be appreciated, thanks.

Upvotes: 0

Views: 79

Answers (2)

Ryan Kempt
Ryan Kempt

Reputation: 4209

This ended up being the solution, after hours of trying, apparently you can use a subquery on a WHERE but not FROM?

CREATE VIEW something AS

SELECT * FROM Transactions AS t
WHERE Date =
(
  SELECT MAX(Date)
  FROM Transactions
  WHERE Machine = t.Machine
)

Upvotes: 1

fancyPants
fancyPants

Reputation: 51868

You don't need a subquery here. You want to have the latest date in the group of machines, right?

So just do

SELECT
t.*, MAX(date)
FROM Transactions t
GROUP BY Machine
ORDER BY Machine ASC /*this line is obsolete by the way, since in MySQL a group by automatically does sort, when you don't specify another sort column or direction*/

A GROUP BY is used together with a aggregate function (in your case MAX()) anyway.

Alternatively you can also specify multiple columns in the ORDER BY clause.

SELECT 
* 
FROM
Transactions
GROUP BY Machine
ORDER BY Date DESC, Machine ASC

should give you also what you want to achieve. But using the MAX() function is definitely the better way to go here.

Actually I have never used a GROUP BY without an aggregate function.

Upvotes: 0

Related Questions