SQL get two items from latest four sets

Question

Yes, this is probably yet another greatest-n-per-group question... But I've tried at least a dozen of answers with the questions already posted here and could not find one that works for what I'm trying to do. It's either probably something really simple I'm overlooking, or what I want is just a bit slightly different. Probably the first :/

I have two tables (InnoDB), one for imagesets which has one row per set with some info like a set_title, what product it is linked to, on/offline status and dateadded:

GallerySets

set_id (primary)
product_id (foreign key)
set_title (varchar 128)
set_status (enum online/offline, index)
set_dateadded (datetime, index)

And a table for the individual images, each set has one or more images. No path field though, I'm just using it kinda like set_id/pic_id.jpg... There are some other fields like viewcounts etc, but those are not needed for this problem so I've left those out.

GalleryPics

pic_id (primary)
set_id (primary, foreign key)
pic_dateadded (datetime, index)

Basically I want to show 2 images for the latest 4 sets. I've got this query below which gets close to what I want, the only thing it doesn't do is limit it to 2 per set:

SELECT s.`set_id`, s.`set_title`, s.`set_dateadded`, p.`pic_id`, p.`pic_dateadded`
FROM `GallerySets` s
LEFT JOIN `GalleryPics` p
ON (s.`set_id` = p.`set_id`)
WHERE s.set_status = 'online'
ORDER BY s.`set_dateadded` DESC, p.`pic_dateadded` ASC

It just shows everything of course. In other questions (like this one) I've seen changing the ON-line and adding HAVING should do the trick:

ON (s.`set_id` = p.`set_id` AND s.`set_dateadded` < p.`pic_dateadded`)
HAVING COUNT(*) < 3

I've tried all sorts of variations on this with HAVING, but it doesn't get me any closer. It's actually returning either zero results or one row per set, as soon as I add any kind of HAVING line to it. Perhaps I'm trying to use the wrong greatest-n-per-group solution? Maybe because I want to sort on a datetime field and not on auto-incrementing IDs?

Wanted results

Actually want more like first three pics from latest five sets, but in order to give a not too long result example... tuned it down a bit.

set_id | set_title | set_dateadded         | pic_id | pic_dateadded
4      | blah4     | 2011-10-04 00:00:00   | 32     | 2011-10-04 12:44:01
4      | blah4     | 2011-10-04 00:00:00   | 33     | 2011-10-04 12:44:02
3      | blah3     | 2011-10-03 00:00:00   | 26     | 2011-10-03 12:33:01
3      | blah3     | 2011-10-03 00:00:00   | 27     | 2011-10-03 12:33:02
2      | blah2     | 2011-10-02 00:00:00   | 11     | 2011-10-02 12:22:01
2      | blah2     | 2011-10-02 00:00:00   | 12     | 2011-10-02 12:22:02
1      | blah1     | 2011-10-01 00:00:00   | 1      | 2011-10-01 12:11:01
1      | blah1     | 2011-10-01 00:00:00   | 2      | 2011-10-01 12:11:02

Starting to think it might not be the worst idea to just have one query get the latest sets and then use a while loop in php with a query to get a few pics per set. :/

Solution

Working solution found thanks to George Psarakis. This shows the first two pics in the latest four sets:

SET @rownum=0;
SET @last_group_id=0;
SELECT gp.pic_id, gp.pic_dateadded, gs.*,
@rownum:=IF(gp.set_id<>@last_group_id,1,@rownum+1) AS rn, @last_group_id:=gp.set_id
FROM GalleryPics gp JOIN 
(
  SELECT * FROM GallerySets WHERE set_status='online'
  ORDER BY gs.set_dateadded DESC LIMIT 5
) AS gs
ON gs.set_id = gp.set_id 
HAVING rn < 4
ORDER BY gs.set_dateadded DESC, gp.pic_dateadded ASC

(Query time is about 0.0030 sec for 900+ sets and 10000+ pics)

georgepsarakis · Accepted Answer · 2011-11-03T07:13:25.060

0

I am not very certain but could you try the following?

SET @rownum=0;
SET @last_group_id=0;
SELECT * FROM (
  SELECT gp.*,gs.*,@rownum:=IF(gp.set_id<>@last_group_id,1,@rownum+1) AS rn,
  @last_group_id:=gp.set_id FROM GalleryPics gp JOIN 
  (
    SELECT * FROM GallerySets WHERE set_status='online' 
    ORDER BY set_dateadded DESC LIMIT 4
  ) AS gs
  ON gs.set_id = gp.set_id 
  GROUP BY gp.set_id 
  ORDER BY gp.set_id,gp.pic_dateadded 
) tmp WHERE rn < 3

edited Nov 03 '11 at 07:13

answered Nov 01 '11 at 20:29

georgepsarakis

1,927
3
20
24

Could not get results in this comment look like how it's on the main post, so I've had to add them at the bottom of the question. It originally showed only 3 rows, I've upped the limit a bit to see what the rownum does, which just keeps going 1,2,3 on each row. It does get the first pic in a set if added to order by, but the rownum doesn't seem to work as intended. It goes +1 on each set, not on each pic in set. So it doesn't show two pics per set, I think it's because the GROUP BY set_id that makes this impossible? Also a bit odd: it skips set 911 and 908, I couldn't figure out why. – Mav Nov 02 '11 at 15:37
geez adding comments with some formatting is a huge pain in the *** here, apologies but can't get this markdown stuff to work in here. – Mav Nov 02 '11 at 15:38
Ah, it skips set 911 and 908 because those don't have status=online, my bad. Having been trying to get this to work with other examples, but I keep running into problems with the 'GROUP BY set_id'. It just won't serve two pics per set if I group it by set, no matter what HAVING rownum is after it. – Mav Nov 02 '11 at 15:59
I have edited my answer. Check out the new version of the query. I have added a UDF variable and added extra nesting and some filtering with the row number. – georgepsarakis Nov 03 '11 at 07:15
At first it gave an error: Duplicate column name 'set_id', so I changed gp.* to gp.pic_id. Then it doesn't give errors but also no results. But after messing around a bit I got some results that looked promising, removed the outer () and tried adding HAVING instead of the GROUP BY. Got the desired result, with the GROUP BY set_id it returns the same but only one row per set. I also tried WHERE rn < 3, but for some reason that gives this error: "Unknown column 'rn' in 'where clause'", even though it works fine as HAVING rn < 3. Odd, but it works now, so a big thank you! – Mav Nov 04 '11 at 14:44
Glad you finally figured it out @Mav ! – georgepsarakis Nov 04 '11 at 17:35
Argh, finally got around to start using the query in the actual php code... And for some weird reason, the query that works in phpmyadmin causes an error when using in php. Both with PHP's mysql_query() and using pear:MDB2 as well. It says there's a syntax error in the first part, might that be the if() part? Still ridiculously weird that it works just fine in phpmyadmin. – Mav Nov 08 '11 at 17:57
Are you using the UDF variables? If so, PHP's mysql extension does not allow multiple SQL statements to be executed (security reasons). I think mysqli does. Nevertheless, you can execute them sequentially it doesn't matter at all; phpmyadmin on the contrary does execute a lot of statements together, probably separates the statements as well, though this is transparent to you. – georgepsarakis Nov 09 '11 at 06:05
Ah that explains it, the SET variable is seen as a separate query, I've split it up and executing it sequentially does work. Thanks again! – Mav Nov 10 '11 at 14:56

SQL get two items from latest four sets

Solution

1 Answers1