Select latest record from right table when using j

问题:

Trying to join two tables and want to get latest record from second.

SELECT * 
FROM gallery 
    LEFT JOIN images_videos ON gallery.ID=images_videos.gallery_id 
WHERE gallery.user_id ="+another_userid+" 
GROUP BY gallery.ID 
ORDER BY gallery.ID DESC

via this query i got the oldest images from images_videos but I want to select latest images from images_videos table Please help I have multiple entries in images_videos for gallery table

i want latest image for each gallery.

回答1:

implied your images_videos table has an ID field

SELECT * 
FROM   gallery g1 
       LEFT JOIN images_videos iv1 
              ON g1.id = iv1.gallery_id 
WHERE  gallery.user_id = "+another_userid+" 
       AND iv1.id = (SELECT Max(id) 
                     FROM   images_videos iv2 
                     WHERE  iv1.gallery_id = iv2.gallery_id) 

回答2:

You can use the custom join to get that :

SELECT * 
FROM gallery 
INNER JOIN
(
    SELECT gallery_id,MAX(images_videoid) AS images_videoid 
    FROM images_video GROUP BY gallery_id 
) LATEST

ON gallery.ID=LATEST.gallery_id 
WHERE .... 
ORDER ....

回答3:

First off, Your current ORDER BY statement sorts by gallery ID, not by image_video ID. So you'll get your galleries in descending order, but the images within those galleries will have no defined order. Since they are probably physically stored in order of creation, that's the order you're receiving them.

I'm confused by the GROUP BY. That's probably not something you want: it creates a single row per gallery. It's not even legal SQL, but I think mysql allows it and gives you random data. So you get a row per gallery with a random images_videos from that gallery. Again, probably the earliest one because that's how it is physically stored.

Drop the GROUP BY and order by images_videos ID. You do have some sort of column on images_videos that lets you determine some kind of order, right?

回答4:

Try this:

SELECT * 
FROM gallery g 
LEFT JOIN (SELECT * 
           FROM images_videos iv 
           INNER JOIN (SELECT iv.gallery_id, MAX(imageVideoId) imageVideoId 
                       FROM images_videos iv GROUP BY iv.gallery_id
                      ) A ON iv.gallery_id = A.gallery_id AND iv.imageVideoId = A.imageVideoId
          ) iv ON g.ID=iv.gallery_id 
WHERE g.user_id ="+another_userid+" 
GROUP BY g.ID 
ORDER BY g.ID DESC

回答5:

this side of code

 SELECT gallery_id,MAX(images_videoid) AS images_videoid 
FROM images_video GROUP BY gallery_id 

bring back the latest row in the images table for each gallery_id (images_videoid is identity, so the latest is gonna be max number)

after that you join result which is a table include (gallery_id,latest images_videoid) on the galleryid to get latest image for each gallery.