Ms Version Of This Mysql View With Group By?

I was shown the ease that one can make a view from multiple tables, GROUPing BY an id of one of the tables in xception's awesome answer here: CREATE VIEW WHERE SELECTid = VIEWrowID

Solution 1:

As MySQL simply picks a random value from the non-grouped columns, the following should do it:

SELECT dbo.table1.column1 AS table1column1,
       min(dbo.table1.column2) AS table1column2,
       min(dbo.table2.column1) AS table2column1,
       min(dbo.table2.column2) AS table2column2
FROM table1, table2
WHERE table2.column1 = table1.column1
GROUPBY table1.column1

I highly recommend you read this blog posting http://rpbouman.blogspot.de/2007/05/debunking-group-by-myths.html to understand what MySQL is doing (wrong)