Jump to content

Is it possible to GROUP BY selects in UNION?


BagoZonde
Go to solution Solved by BagoZonde,

Recommended Posts

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 by BagoZonde
Link to comment
Share on other sites

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 by BagoZonde
Link to comment
Share on other sites

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.

Link to comment
Share on other sites

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 by BagoZonde
Link to comment
Share on other sites

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

Link to comment
Share on other sites

  • Solution

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 by BagoZonde
Link to comment
Share on other sites

This thread is more than a year old. Please don't revive it unless you have something important to add.

Join the conversation

You can post now and register later. If you have an account, sign in now to post with your account.

Guest
Reply to this topic...

×   Pasted as rich text.   Restore formatting

  Only 75 emoji are allowed.

×   Your link has been automatically embedded.   Display as a link instead

×   Your previous content has been restored.   Clear editor

×   You cannot paste images directly. Upload or insert images from URL.

×
×
  • Create New...

Important Information

We have placed cookies on your device to help make this website better. You can adjust your cookie settings, otherwise we'll assume you're okay to continue.