CODEWITHSUNDEEP
mysql
Carl Waddington
Carl Waddington

Reputation: 72

Mysql max date within a subquery

i cant work out how to get this subquery working to get the latest date for the row.

SELECT Thread_Heading.*, Thread_Articles.*, Thread_ArticlesPost.* 
FROM Thread_Heading 
LEFT JOIN Thread_Articles 
    ON Thread_Articles.Thread_Article_Head_id=Thread_Heading.Thread_Head_id 
LEFT JOIN 
(
    SELECT Thread_ArticlesPost.* 
    FROM Thread_ArticlesPost 
    ORDER BY Thread_ArticlePost_DT DESC
) Thread_ArticlesPost 
    ON  Thread_ArticlesPost.Thread_ArticlePost_Article_id=Thread_Articles.Thread_Article_id  
WHERE Thread_Head_Level = '5' 
GROUP BY Thread_Heading.Thread_Head_id 
ORDER BY Thread_ArticlePost_DT DESC

I need to order the article post date by the latest post for each Thread article. Fairly new to sql and php i just cant work this one out any help would be appreciated.

T

ABLE: Thread_Heading | 
Thread_Head_id
Thread_Head_Name
Thread_Head_Type
Thread_Head_Creator
Thread_Head_Date
Thread_Head_Level

TABLE: Thread_Articles | 
Thread_Article_id
Thread_Article_Head_id
Thread_Article_Creator
Thread_Article_DT
Thread_Article_Level
Thread_Article_Type
Thread_Article_Title

TABLE: Thread_ArticlesPost | 
Thread_ArticlePost_id
Thread_ArticlePost_Head_id
Thread_ArticlePost_Article_id
Thread_ArticlePost_Creator
Thread_ArticlePost_DT
Thread_ArticlePost_Level
Thread_ArticlePost_Type
Thread_ArticlePost_Title
Thread_ArticlePost_Content

I need to display the date like so:

Head Name | Article title | ORDER BY LATEST ArticlePost DT | ArticlePost Creator

The reason i used left joins was to get the left data even if there are no article or article replys.

Appreciate the help.

Have used this to display data Thanks to Blue

SELECT *
FROM
( SELECT th.Thread_Head_Name,
ta., tp1.maxdate, tp2. FROM Thread_Heading th LEFT JOIN Thread_Articles ta ON th.Thread_Head_id = ta.Thread_Article_Head_id LEFT JOIN
( SELECT max(Thread_ArticlePost_DT) maxDate,
Thread_ArticlesPost.* FROM Thread_ArticlesPost GROUP BY Thread_ArticlePost_Article_id ) tp1 ON tp1.Thread_ArticlePost_Article_id=ta.Thread_Article_id
LEFT JOIN Thread_ArticlesPost tp2 ON tp1.Thread_ArticlePost_Article_id = tp2.Thread_ArticlePost_Article_id AND tp1.maxdate = tp2.Thread_ArticlePost_DT
WHERE th.Thread_Head_Level = '5' ORDER BY tp1.maxdate DESC) m GROUP BY Thread_Head_Name ORDER BY Thread_ArticlePost_DT DESC

Upvotes: 1

Views: 2411

Answers (2)

Taryn
Taryn

Reputation: 247610

Without seeing your full table schemas you will want do something similar to this:

SELECT th.*, ta.*, tp.* 
FROM Thread_Heading th
LEFT JOIN Thread_Articles ta
    ON th.Thread_Head_id = ta.Thread_Article_Head_id
LEFT JOIN 
(
    SELECT max(Thread_ArticlePost_DT) maxDate, Thread_ArticlesPost.* 
    FROM Thread_ArticlesPost 
    GROUP BY Thread_ArticlePost_Article_id
) Thread_ArticlesPost tp
    ON  tp.Thread_ArticlePost_Article_id=ta.Thread_Article_id  
WHERE Thread_Head_Level = '5' 
GROUP BY th.Thread_Head_id 
ORDER BY tp.maxdate DESC

Based on your edit the following should return the data you want:

SELECT th.Thread_Head_Name, 
  ta.Thread_Article_Title,
  tp1.maxdate,
  tp2.Thread_ArticlePost_Creator
FROM Thread_Heading th
LEFT JOIN Thread_Articles ta
    ON th.Thread_Head_id = ta.Thread_Article_Head_id
LEFT JOIN 
(
    SELECT max(Thread_ArticlePost_DT) maxDate, 
      Thread_ArticlesPost.Thread_ArticlePost_Article_id
    FROM Thread_ArticlesPost 
    GROUP BY Thread_ArticlePost_Article_id
) tp1
  ON  tp1.Thread_ArticlePost_Article_id=ta.Thread_Article_id  
LEFT JOIN Thread_ArticlesPost tp2
  ON tp1.Thread_ArticlePost_Article_id = tp2.Thread_ArticlePost_Article_id
  AND tp1.maxdate = tp2.Thread_ArticlePost_DT 
WHERE th.Thread_Head_Level = '5' 
ORDER BY tp1.maxdate DESC

Edit #2, based on your comments, I think the below query should resolve any remaining issues:

SELECT th.Thread_Head_id,
  th.Thread_Head_Name,
  ta.Thread_Article_Title,
  tp.Thread_ArticlePost_Creator,
  tap.MaxPostDate
FROM Thread_Heading th 
LEFT JOIN
(
  SELECT max(ta.Thread_Article_DT) MaxArticleDate, 
    ta.Thread_Article_Head_id,
    max(tp.Thread_ArticlePost_DT) MaxPostDate
  FROM Thread_Articles ta
  LEFT JOIN Thread_ArticlesPost tp
    ON ta.Thread_Article_id = tp.Thread_ArticlePost_Article_id
  GROUP BY Thread_Article_Head_id
) tap
  ON th.Thread_Head_id = tap.Thread_Article_Head_id 
LEFT JOIN Thread_Articles ta
  ON tap.Thread_Article_Head_id = ta.Thread_Article_Head_id
  AND tap.MaxArticleDate = ta.Thread_Article_DT
LEFT JOIN Thread_ArticlesPost tp
  ON tap.MaxPostDate = tp.Thread_ArticlePost_DT
WHERE th.Thread_Head_Level = '5'
ORDER BY MaxPostDate desc

See SQL Fiddle With Demo

Upvotes: 3

John Woo
John Woo

Reputation: 263683

You don't have to have a GROUP BY clause outside side your post are already grouped on the subquery based on your latest post. Try this one,

SELECT  a.*, b.*, c.* 
FROM    Thread_Heading a
            LEFT JOIN Thread_Articles b
                ON b.Thread_Article_Head_id = a.Thread_Head_id
            LEFT JOIN Thread_ArticlesPost c
                ON c.Thread_ArticlePost_Article_id = b.Thread_Article_id 
            LEFT JOIN 
            (
                SELECT      Thread_ArticlePost_Article_id, MAX(Thread_ArticlePost_DT) maxDate
                FROM        Thread_ArticlesPost 
                GROUP BY    Thread_ArticlePost_Article_id
            ) d ON  d.Thread_ArticlePost_Article_id = b.Thread_Article_id AND
                    c.Thread_ArticlePost_DT = d.maxDate
WHERE       Thread_Head_Level = '5' 
-- GROUP BY a.Thread_Head_id 
ORDER BY    Thread_ArticlePost_DT DESC

Upvotes: 0

Related Questions