Menu
The query below seems to LIMIT all the results when it's being LEFT JOINed, so the total in the subquery is just 1. How can I make it LIMIT so that I get a 1:1 match between projects rows and the last date stored in projects_hours_archive which stores records of projects.projected_hours that are collected on a cron job once per week?
projected_hours_archive has columns: id, project_id, hours and datetime.
SELECT
GROUP_CONCAT( projected_hours_last.date, '|', projected_hours_last.number ) AS 'projected_last_info'
FROM
projects
LEFT JOIN (
SELECT *
FROM
projected_hours_archive
ORDER BY date DESC
LIMIT 1
) AS projected_hours_last ON ( projected_hours_last.project_id = projects.id )
WHERE projected_hours > 0
GROUP BY projects.id
I tried to adopt using MySQL Limit LEFT JOIN Subquery after joining but wasn't successful. If I remove the LIMIT in the subquery I get too many results.
573
In this article, we will learn how to limit query results in SQL using different examples. A MySQL supports the LIMIT clause to select a limited number of records. If we want to LIMIT the number of results that will return us a number of rows then we simply use the LIMIT command. Use the below SQL statement to create a database called geeks:
Summary: in this tutorial, we will introduce you another kind of joins called SQL LEFT JOIN that allows you to retrieve data from multiple tables. In the previous tutorial, you learned about the inner join that returns rows if there is, at least, one row in both tables that matches the join condition.
Limiting query results in JPA is slightly different to SQL – we don't include the limit keyword directly into our JPQL. Instead, we just make a single method call to Query#maxResults or include the keyword first or top in our Spring Data JPA method name. As always, you can find the code over on GitHub.
Because non-matching rows in the right table are filled with the NULL values, you can apply the LEFT JOIN clause to miss-match rows between tables. For example, to find the country that does not have any locations in the locations table, you use the following query:
use group by in the sub query and get maximum date per project.
EDIT: as per the OP comment, adding second max date.
The trick from this mysql how to get 2nd highest value with group by and in a left join is used.
SELECT
GROUP_CONCAT( projected_hours_last.secondMaxDate, '|', projected_hours_last.number ) AS 'projected_last_info'
FROM
projects
LEFT JOIN (
SELECT project_id, max(date) as maxDate,
substring_index(substring_index(group_concat(date order by date desc), ',', 2), ',', -1
) as secondMaxDate
FROM
projected_hours_archive
group by project_id
) AS projected_hours_last ON ( projected_hours_last.project_id = projects.id )
WHERE projected_hours > 0
GROUP BY projects.id
I had the same question.
I suppose your field projects.project_id is unique (so there are no duplicates in that table). Otherwise, you can use DISTINCT(projects.project_id) or GROUP BY projects.project_id as you've used before.
Solution #1 (using GROUP BY while joining):
SELECT a1.project_id,
GROUP_CONCAT(a2.date, '|', a2.number ) AS 'projected_last_info'
FROM projects a1
LEFT JOIN (
SELECT b1.project_id, b1.date, b1.number
FROM projected_hours_archive b1
ORDER BY b1.date DESC
GROUP BY b1.project_id
) a2 ON a2.project_id = a1.project_id
WHERE a1.projected_hours > 0
Solution #2 (using LIMIT directly in subquery in SELECT statement):
SELECT a1.project_id,
(SELECT GROUP_CONCAT(a2.date, '|', a2.number )
FROM projected_hours_archive b1
WHERE b1.project_id = a1.project_id
ORDER BY b1.date DESC
) AS 'projected_last_info'
FROM projects a1
WHERE a1.projected_hours > 0
If you love us? You can donate to us via Paypal or buy me a coffee so we can maintain and grow! Thank you!
Donate Us With
