0

I have a SQL query with a left join:

SELECT i.id
     , i.user_id
     , i.date
     , h.cat_id
  FROM io__image i
  LEFT 
  JOIN io__image_cat_hext h
    ON h.image_id = i.id
 WHERE i.home_granted >= 1 
   AND user_id = 40
 ORDER 
    BY i.date DESC

Result looks like:

id   user_id          date       cat_id     
530     40  2018-05-12 20:45:54     42
528     40  2018-05-11 22:59:17     42
518     40  2018-05-11 17:22:30     42
508     40  2018-05-09 13:45:40     37
504     40  2018-05-06 22:40:31     37
492     40  2018-05-02 21:21:20     37
490     40  2018-05-02 16:16:09     36
481     40  2018-05-02 15:08:35     36

Would it be possible to get DISTINCT cat_id? I tried with Group by:

SELECT i.id
     , i.user_id
     , i.date
     , h.cat_id
  FROM io__image i 
 GROUP 
    BY h.cat_id
  LEFT 
  JOIN io__image_cat_hext h
    ON h.image_id = i.id
WHERE i.home_granted >= 1 
   AND user_id = 40
 ORDER 
    BY i.date DESC

But I get this error:

Syntax near 'LEFT JOIN io__image_cat_hext ON io__image_cat_hext.image_id = io__image.id WHER' on line 4

D-Shih
  • 44,943
  • 6
  • 31
  • 51
London Smith
  • 1,622
  • 2
  • 18
  • 39
  • if you want distinct cat_id then what value of id do you want to represent. for eg for cat_id 42 , what id do u want 530 or 528. Attaching a required output will be helpful in understanding your question – G.Arima May 26 '18 at 15:21
  • group by cat_id ? – NiVeR May 26 '18 at 15:23
  • See https://meta.stackoverflow.com/questions/333952/why-should-i-provide-an-mcve-for-what-seems-to-me-to-be-a-very-simple-sql-query – Strawberry May 26 '18 at 15:26
  • If you want the most recent row per `cat_id` - here is your answer: https://stackoverflow.com/questions/7745609/sql-select-only-rows-with-max-value-on-a-column – Paul Spiegel May 26 '18 at 15:31
  • Edit your question and provide the results you want. It is very unclear. – Gordon Linoff May 26 '18 at 15:35

2 Answers2

2

It's hard to understand what you exactly mean by DISTINCT cat_id in this context. Maybe you are searching for something like this?

SELECT io__image.id,
       io__image.user_id,
       io__image.date,
       io__image_cat_hext.cat_id
       FROM (SELECT max(io__image_cat_hext.image_id) image_id,
                    io__image_cat_hext.cat_id
                    FROM io__image_cat_hext
                    GROUP BY io__image_cat_hext.cat_id) io__image_cat_hext
            INNER JOIN io__image
                       ON io__image.id = io__image_cat_hext.image_id
       ORDER BY io__image.date DESC;

It'll give you only the image with the highest id for each category. If the ids are auto incremented, that's the image inserted most recently.

sticky bit
  • 36,626
  • 12
  • 31
  • 42
  • Yes, sorry I want last images added from different categories. I have an SQL error from your query : Syntax error at line 6 near 'io__image_cat_hext.cat_id FROM io__image_cat_hext' – London Smith May 26 '18 at 15:51
  • @LondonSmith: I missed a comma. That's why you should include DDL of the tables in your questions. I could have tested it then. Anyway, see my edit. I also changed `min` to `max` as I understand that is what you might mean by "last added". – sticky bit May 26 '18 at 15:56
  • That's really perfect, thanks million. I don't know how to include DDL, I am not aware about that. – London Smith May 26 '18 at 15:58
  • @LondonSmith: Welcome. By DDL I mean especially the `CREATE TABLE ...` statements. You should have them if you created the database yourself. Otherwise see the documentation of your SQL client how to generate them from existing tables. E.g. in MySQL Workbench you can right click a table in the tree on the left and choose "Copy to Clipboard" > "Create Statement", to copy the `CREATE ...` in the clipboard. – sticky bit May 26 '18 at 16:03
1

If you want only the cat_id values, then you can do:

select h.cat_id
from io__image i join
     io__image_cat_hext h
     on h.image_id = i.id
where i.home_granted >= 1 and i.user_id = 40
group by h.cat_id
order by max(i.date) desc;

Without showing the results you want, this seems like the best fit to your question.

Gordon Linoff
  • 1,242,037
  • 58
  • 646
  • 786