0

I need help in writing my MAX() in filter of my files. I have searched everywhere and found that have to write a sub query. Here is my query and I need to put a sub query:

SELECT webform_views_news_webform_798.clipping_type AS webform_views_news_webform_798_clipping_type, webform_views_news_webform_798.news_heading AS webform_views_news_webform_798_news_heading, webform_views_news_webform_798.news_detail AS webform_views_news_webform_798_news_detail, webform_views_news_webform_798.newspaper AS webform_views_news_webform_798_newspaper, webform_views_news_webform_798.news_url AS webform_views_news_webform_798_news_url, webform_submissions.sid AS sid, webform_views_news_webform_798.news_date AS webform_views_news_webform_798_news_date_1, MAX(webform_views_news_webform_798.news_date) AS webform_views_news_webform_798_news_date
FROM 
{webform_submissions} webform_submissions
LEFT JOIN {webform_views_news_webform_798} webform_views_news_webform_798 ON webform_submissions.sid = webform_views_news_webform_798.sid
WHERE **(( (webform_views_news_webform_798.news_date LIKE '2013-08-23' ESCAPE '\\') ))**
GROUP BY webform_views_news_webform_798_clipping_type, webform_views_news_webform_798_news_heading, webform_views_news_webform_798_news_detail, webform_views_news_webform_798_newspaper, webform_views_news_webform_798_news_url, sid, webform_views_news_webform_798_news_date_1
ORDER BY webform_views_news_webform_798_news_date_1 DESC 

The where clause should be like this:

SELECT webform_views_news_webform_798.clipping_type AS webform_views_news_webform_798_clipping_type, webform_views_news_webform_798.news_heading AS webform_views_news_webform_798_news_heading, webform_views_news_webform_798.news_detail AS webform_views_news_webform_798_news_detail, webform_views_news_webform_798.newspaper AS webform_views_news_webform_798_newspaper, webform_views_news_webform_798.news_url AS webform_views_news_webform_798_news_url, webform_submissions.sid AS sid, webform_views_news_webform_798.news_date AS webform_views_news_webform_798_news_date_1, MAX(webform_views_news_webform_798.news_date) AS webform_views_news_webform_798_news_date
FROM 
{webform_submissions} webform_submissions
LEFT JOIN {webform_views_news_webform_798} webform_views_news_webform_798 ON webform_submissions.sid = webform_views_news_webform_798.sid
**WHERE (( (webform_views_news_webform_798.news_date IN (SELECT MAX(news_date) from webform_views_news_webform_798 )) ))**
GROUP BY webform_views_news_webform_798_clipping_type, webform_views_news_webform_798_news_heading, webform_views_news_webform_798_news_detail, webform_views_news_webform_798_newspaper, webform_views_news_webform_798_news_url, sid, webform_views_news_webform_798_news_date_1
ORDER BY webform_views_news_webform_798_news_date_1 DESC

How can I change this easily without using code?

  • What module generated this query? Views? If so, describe what filters you have used. – Mołot Sep 4 '13 at 7:01
  • i used views to generate this query. the fileter i have used is (webform_views_news_webform_798.news_date LIKE '2013-08-23') and i want to convert this filter to: (webform_views_news_webform_798.news_date IN (SELECT MAX(news_date) from webform_views_news_webform_798 )) – Syed Sana ul Haq Fazli Sep 4 '13 at 8:48
  • how can i chnage this easily withour using the coding. you can't as far as I know. If you want to alter the query, you need to use the tools available to you (PHP in a custom module in this case) – Clive Sep 4 '13 at 9:27
  • please guide me how to do that? – Syed Sana ul Haq Fazli Sep 4 '13 at 16:41

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

Browse other questions tagged or ask your own question.