-2

I am having difficulty creating an SQL statement that selects the 5 most recent subcategories determined by when the content associated with the subcategory was recently created.

Subcategories Table:

subcategory_id Title
33 Fitness
34 Evolution
35 Farming
36 Programming
37 Art
38 Funny

content_subcategories Table:

content_id Subcategory_id
15 34
16 35
16 36
16 37
17 35
18 38
18 34
19 37

Content Table:

content_id date
14 6-5-22
15 8-5-22
16 3-3-22
17 2-5-22
18 10-5-22
19 11-3-22

What I need (limit 10)

subcategory_id subcategory_title content_date (desc)
37 Art 11-3-22
38 Evolution 10-5-22
34 Funny 10-5-22
35 Farming 3-5-22
36 Programming 3-5-22

Notice Evolution does not appear twice so there is DISTINCT applied. The goal here is to pick 10 most recent content, somehow join to subcategory table via content_subcategory table. Remove any duplicate subcategory results.

Mit Patel
  • 7
  • 5
  • 1
    please provide a [minimal reproducible example](https://stackoverflow.com/help/minimal-reproducible-example) with emphasis on minimal – learning Aug 19 '22 at 01:07
  • 1
    Please ask 1 specific researched non-duplicate question. Please either ask about 1 bad query/function with the obligatory [mre] & why you think it should return something else at the 1st subexpression that it doesn't give what you expect justified by reference to authoritative documentation or ask about your overall goal giving working parts you can do & ideally a [mre]. But please ask about the former 1st because misconceptions in the former will get in the way of understanding the latter. And bad code doesn't tell us what you wish it would do. [ask] [Help] – philipxy Aug 19 '22 at 01:45
  • When pinned down & clearly expressed this will be a faq. (Clearly.) Please before considering posting: Pin down code issues via [mre]. Read the manual/reference & google any error message & many clear, concise & precise phrasings of your question/problem/goal, with & without your particular names/strings/numbers, 'site:stackoverflow.com' & tags; read many answers. Reflect research in posts. SO/SE search is poor & literal & unusual, read the help. Google re googling/searching, including Q&A at [meta] & [meta.se]. [How much research effort?](https://meta.stackoverflow.com/q/261592/3404097) – philipxy Aug 19 '22 at 01:50
  • "determined by" & "somehow" are not clear. It's not clear how your desired result is a function of inputs. Don't expect us to guess from example data. Use enough words, sentences & references to parts of examples to clearly & fully say what you mean. To describe a result: Say enough that someone could go away & come back with a solution & a user knows how to use it. When giving a business relation(ship)/association or table (base or query result), say what a row in it states about the business situation in terms of its column values. PS "giving working parts you can do & ideally a [mre]" – philipxy Aug 19 '22 at 01:58
  • Returning the top n rows per group is an easily found faq. There is even a tag for it [tag:greatest-n-per-group]. Returning just one row per subrow group is also an easily found faq. (Notice that you don't say which row you want back when a subrow group has more than one row.) PS Please don't ask people to write your code for you. Dumps of specifications are off-topic. [Help] PS Again: Please give "minimal" representative data in any code question. ("emphasis on minimal") PS [Re SQL querying.](https://stackoverflow.com/a/33952141/3404097) – philipxy Aug 19 '22 at 02:10

2 Answers2

0

Please try the query

SELECT TOP 5 subcategories.*, MAX(content.date) as contentdate FROM subcategories, content_subcategories, content WHERE content.content_id = content_subcategories.content_id AND content_subcategories.subcategories_id = subcategories.subcategory_id GROUP BY subcategory_id, title

It is advisable not to use 'date' as the column name. You can add order by or group by at the end

learning
  • 608
  • 5
  • 15
0

Probably you're looking for something like

SELECT DISTINCT s.subcategory_id, s.Title, c.date
  FROM (
    SELECT subcategory_id, max(date) as last_entry
      FROM content c
     INNER JOIN content_subcategories cs
        ON c.content_id = cs.content_id
     GROUP BY subcategory_id
  ) latest 
 INNER JOIN content_subcategories cs ON latest.subcategory_id = cs.subcategory_id 
 INNER JOIN content c ON c.content_id = cs.content_id AND c.date = latest.last_entry
 INNER JOIN subcategories s ON s.subcategory_id = latest.subcategory_id
ORDER BY c.date DESC
LIMIT 5;

Edit: Corrected the limit to 5. Notice, that this is MySQL syntax (as you didn't provide any information about the DBMS you're using).

Mihe
  • 2,270
  • 2
  • 4
  • 14