Is it possible to GROUP BY selects in UNION?

[BagoZonde]

Hello,

 

I have googled everywhere so I suppose it's impossible but it's better to ask you, MySQL/PHP Freak gurus :].

 

I have two tables: table1 and table2. They represents content which are very different in structure, but both contains in common these fields: id, title, url and publish.

 

I want to display list of contents: last 10 records from table1 to the left side and last 10 records from table2 to the right side.

 

It's very simple with two queries. Just some:

SELECT id, title, url FROM table1 WHERE publish=1 ORDER BY id DESC LIMIT 0, 10

However I'm concerned if it's possible to achieve to have two selects in single query (some UNION maybe) and GROUP by SELECT (something like GROUP BY table comes to my mind). Maybe there's some trick with LEFT JOIN, etc. I'm just curious.

Edited May 15, 2013 by BagoZonde

[CrossMotion]

If you want the last 20 results from both tables combined, you should use UNION. If you want the last 10 records of table 1 and the last 10 records of table 2, I would just run the 2 queries and merge the data later if needed.

[BagoZonde]

While iteratation of results I want to know from which table that records comes so I need some identification like GROUP. Two separate queries is what I got for now but I'm not spellbound and just looking for one query (it's for practice and exploring new things in MySQL you know :]). This is my dream-loop in general:

foreach($result as $group){
   //Each column starts there, no matter if no records at all in first or second table
   print '<div class="column">';
   foreach($group as $tableName=>$record){
      //Let's rock with results for single table
      print '<div class="box box_' . $tableName . '">' . $record['title'] . '</div>';
   }
   print '</div>'; //End of column div
}

Edited May 15, 2013 by BagoZonde

[CrossMotion]

You can try this:

SELECT id, title, url, 'table1' AS table FROM table1 WHERE publish=1 ORDER BY id DESC LIMIT 10
UNION
SELECT id, title, url, 'table2' AS table FROM table2 WHERE publish=1 ORDER BY id DESC LIMIT 10

I havent tested it, but it should return the first 10 results of both tables and add a field named table to each record to identify wich table the record came from.

[BagoZonde]

Thanks a lot, CrossMotion. I remember that I saw that attempt with additional field. So thanks to your hint, I do it that way as I have problems with LIMIT, but first that's a query that works well:

SELECT "table1" AS type, id, title, url FROM table1
UNION
SELECT "table2" AS type, id, title, url FROM table2
ORDER BY id DESC

One thing about this query I'm not sure: I'm using PDO in PDO::FETCH_GROUP mode so first column determine grouping into array for "table1" and "table2" where results are nested.

 

So no GROUP BY type was used there (and I'm not sure in regular mysql_ or mysqli_ GROUP BY would be needed). When I've added "GROUP BY type" of that query, it returns all results of "table1" and only one result of "table2"?! That's wrong because there should be more for sure! I was also trying with UNION ALL without luck.

 

When trying to execute your query, I got error from PDO:

 

SQLSTATE[HY000]: General error: 1221 Incorrect usage of UNION and ORDER BY

 

It seems that selects must be in parentheses when ORDER BY used (that same for LIMIT), however when parantheses were added, it returns no results for this query:

(SELECT "table1" AS type, id, title, url FROM table1 ORDER BY id DESC LIMIT 0, 10)
UNION
(SELECT "table2" AS type, id, title, url FROM table2 ORDER BY id DESC LIMIT 0, 10)

My dream-loop is still sad ;].

Edited May 15, 2013 by BagoZonde

[Barand]

You use GROUP BY when aggregating data - it returns 1 row for each value

 

eg. You have a team of salesmen each with hundreds of sales records

 

SELECT salesman, SUM(salesvalue) as totalSales

FROM sales

GROUP BY salesman

 

gives 1 row per salesman with their total sales

[BagoZonde]

Ok, finally it's done. I was using some old extension for PDO written one year ago where some fetches were treated in other way. That means I was looking in wrong place but I've tested it with mysqli and that kind of SELECT was executed well.

 

So, thanks to fetchAll(PDO::FETCH_GROUP) I'm getting two arrays (first for table1 and second one for table2) with data inside them. So this is the winner query for 100% clarity once again:

(SELECT "table1" AS type, id, title, url FROM table1 ORDER BY id DESC LIMIT 0, 10)
UNION
(SELECT "table2" AS type, id, title, url FROM table2 ORDER BY id DESC LIMIT 0, 10)

My dream-loop is happy now :].

 

Thanks guys for help, especially for CrossMotion for hint with "fake" column name. That's a key!

 

I'm not sure if this method is possible with mysqli as I want to get results in two arrays for each table, however PDO makes it possible easy way :].

I hope it will help somebody else in future.

Thanks!

Edited May 16, 2013 by BagoZonde