[RESOLVED] group by / order by problem

[perpetualshaun]

I am pulling a list of package delivery data, and there are multiple records for the same package, all with same pkg_label. This query does some of what I'm after:

SELECT
		p.*, max(pkg_timestamp) max_pkg_timestamp,
		c.customer_name,
		l.lab_name
	FROM
		raco_trip_details p
	LEFT JOIN
		customers c on c.customer_id=p.pkg_customer_id
	LEFT JOIN
		labs l on l.lab_id=p.pkg_lab_id
	WHERE
		1

GROUP BY
	pkg_label
ORDER BY
	max_pkg_timestamp DESC, pkg_label ASC

Really the only problem is that it's not pulling the most recent pkg_status and pkg_timestamp values. The 'max(pkg_timestamp)' part is working as the most recent record is indeed at the top of the results, and it's grouping by the pkg_label field as designed.

But how do I also get it to pull the most recent time (field pkg_timestamp, which is an integer) and the most recent status (field pkg_status, which is a string)?

[johanafm]

All columns should be either part of the group by clause or be used with an aggregate functions. MySQL allows you to not do this, but the results are unpredictable.

Doing a max() or min(pkg_status) is one way to go if it's applicable for the data stored. Else you'd have to group by pkg_label, pkg_status and deal with it from there.

[perpetualshaun]

I'm still stuck on this one, did some more searching and trying other things, and even tried making something work with subqueries this morning:

SELECT
	p.pkg_label, p.pkg_route_number, p.pkg_stop_number,
	c.customer_name,
	l.lab_name,
	r.*
FROM
	raco_trip_details p
LEFT JOIN
	customers c on c.customer_id=p.pkg_customer_id
LEFT JOIN
	labs l on l.lab_id=p.pkg_lab_id
LEFT JOIN
	(
		SELECT
			pkg_status, pkg_timestamp
		FROM
			raco_trip_details
		ORDER BY
			pkg_timestamp DESC
		LIMIT 1
	) r
	ON r.pkg_label=p.pkg_label
WHERE
	1
GROUP BY
	pkg_label
ORDER BY
	r.pkg_timestamp DESC, pkg_label ASC

But I get this error: #1054 - Unknown column 'r.pkg_label' in 'on clause'

We are running MySQL 5.0.58 and from what I've seen from other examples online, what I have above should work and I don't understand the error I'm getting back. Any other ideas?

There's got to be some way to group by the label name so that it only appears ONCE in my result set, and pulls the most recent timestamp and the appropriate status - tied to the most recent timestamp.

[johanafm]

(SELECT pkg_status, pkg_timestamp ... AS r)

You only have r.pkg_status and r.pkg_timestamp

[perpetualshaun]

Weird... this works, but only seems to do the LEFT JOIN subquery on the 1st result. All the other rows show NULL down the column for the r.pkg_label, r.pkg_timestamp and r.pkg_status fields. Here's my query:

SELECT
	p.pkg_label, p.pkg_route_number, p.pkg_stop_number,
	c.customer_name,
	l.lab_name,
	r.*
FROM
	raco_trip_details p
LEFT JOIN
	customers c on c.customer_id=p.pkg_customer_id
LEFT JOIN
	labs l on l.lab_id=p.pkg_lab_id
LEFT JOIN
	(
		SELECT
			pkg_label, pkg_status, pkg_timestamp
		FROM
			raco_trip_details
		ORDER BY
			pkg_timestamp DESC
		LIMIT 1
	) AS r
	ON r.pkg_label=p.pkg_label
WHERE
	1
GROUP BY
	pkg_label
ORDER BY
	r.pkg_timestamp DESC, p.pkg_label ASC

Any ideas?

[johanafm]

perpetualshaun;10928818 wrote:

There's got to be some way to group by the label name so that it only appears ONCE in my result set, and pulls the most recent timestamp and the appropriate status - tied to the most recent timestamp.

Going back to the original query you posted, try something along the lines of

SELECT sub.pkg_label, sub.max_pkg_timestamp, rtp.pkg_status
FROM (
	SELECT pkg_label, max(pkg_timestamp) max_pkg_timestamp
	FROM raco_trip_details AS p
		LEFT JOIN customers AS c on c.customer_id = p.pkg_customer_id
		LEFT JOIN labs AS l on l.lab_id = p.pkg_lab_id
		WHERE 1
		GROUP BY pkg_label
		ORDER BY max_pkg_timestamp DESC, pkg_label ASC
) AS sub
INNER JOIN raco_trip_details AS rtp ON rtp.pkg_timestamp = sub.max_pkg_timestamp

The nested query does exactly the same thing as your first query did, except that it only pulls data that is either part of an aggregate function (pkg_timestamp) or part of the GROUP BY clause (pkg_label).
The outer query joins information from the nested query together with raco_trip_details (which I assumed is the one storing pkg_timestamp and pkg_status) on pkg_timestamp and the max_pkg_timestamp retrieved earlier. Now you only get the rows with the appropriate information, and as such you can also retrieve the pkg_status.

But if I understood your query correctly, take it one step further and

[code]
SELECT sub.pkg_label, sub.max_pkg_timestamp, rtp.pkg_status, c.customer_name, l.lab_name
FROM (
	SELECT pkg_label, max(pkg_timestamp) max_pkg_timestamp
	FROM raco_trip_details AS p
	GROUP BY pkg_label
	ORDER BY max_pkg_timestamp DESC, pkg_label ASC
) AS sub
	INNER JOIN raco_trip_details AS rtp ON rtp.pkg_timestamp = sub.max_pkg_timestamp
	LEFT JOIN customers AS c on c.customer_id = rtp.pkg_customer_id
	LEFT JOIN labs AS l on l.lab_id = rtp.pkg_lab_id

[perpetualshaun]

GREAT! Thanks for your help johanafm! This works perfectly:

	SELECT
		rtp.pkg_label, sub.pkg_timestamp, rtp.pkg_status, c.customer_name, l.lab_name
	FROM (
		SELECT
			max(pkg_timestamp) pkg_timestamp
		FROM
			raco_trip_details AS p
		GROUP BY
			pkg_label
		ORDER BY
			pkg_timestamp DESC
	) AS sub
	INNER JOIN 
		raco_trip_details AS rtp ON rtp.pkg_timestamp=sub.pkg_timestamp
	LEFT JOIN
		customers AS c on c.customer_id = rtp.pkg_customer_id
	LEFT JOIN
		labs AS l on l.lab_id = rtp.pkg_lab_id
	WHERE
		1
	ORDER BY
		pkg_timestamp DESC

Thanks again!