Nathan Riley
asked on
mySQL Query Syntax
I have the following query that works, but doesn't provide the desired output. As you can see I'm getting the data for company_id 2, 7, and 11 and ordering by the post_date. The problem is that if say company #2 has 10 posts that are the most recent by post_date then my limited 5 results are all from the same company.
Is there a way to modify the query so I'll receive results from all companyID's within my limit?
Is there a way to modify the query so I'll receive results from all companyID's within my limit?
select fp.id as postID, fp.company_id as coID, post, date_format(post_date,'%m/%d/%Y %h:%i %p') postDate, postMedia, name
from fo_posts fp
inner join fo_company co on fp.company_id = co.id
where company_id in (2,7,11)
order by post_date desc
limit 5
Which tables do these columns come from (you should specify ALL columns with their source)
fp? co?
, ?.post
, date_format( ?.post_date, '%m/%d/%Y %h:%i %p') postDate
, ?.postMedia
, ?.NAME
fp? co?
, ?.post
, date_format( ?.post_date, '%m/%d/%Y %h:%i %p') postDate
, ?.postMedia
, ?.NAME
e.g.
SELECT tp.id
,tp.company_id
,tp.post
,date_format(tp.post_date, '%m/%d/%Y %h:%i %p') postDate
,tp.postMedia
,tp.NAME
FROM (
SELECT fp.id
,fp.company_id
,fp.post
,fp.postDate
,fp.postMedia
,fp.NAME
FROM fo_posts fp
WHERE company_id = 2
ORDER BY fp.post_date DESC limit 2
UNION ALL
SELECT fp.id
,fp.company_id
,fp.post
,fp.postDate
,fp.postMedia
,fp.NAME
FROM fo_posts fp
WHERE company_id = 7
ORDER BY fp.post_date DESC limit 2
UNION ALL
SELECT fp.id
,fp.company_id
,fp.post
,fp.postDate
,fp.postMedia
,fp.NAME
FROM fo_posts fp
WHERE company_id = 11
ORDER BY fp.post_date DESC limit 2
) AS tp
/*
JOIN here, if needed
*/
ORDER BY tp.post_date DESC limit 5
ASKER CERTIFIED SOLUTION
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
>>"getting the data for company_id 2, 7, and 11 ...
Is there a way to modify the query so I'll receive results from all companyID's within my limit?"
The original requirement was for 3 companies to be present in the top 5 results
when did the requirements change?
Is there a way to modify the query so I'll receive results from all companyID's within my limit?"
The original requirement was for 3 companies to be present in the top 5 results
when did the requirements change?
then get top 5 from that list of 6