I’d like to MAX the time_stamp coming from the z alias.
Am correct in thinking that I need to apply the pattern in the subset alias to the z alias?
here’s my code so far:
SELECT id
, z.route_order
, z.time_stamp
, nconnectz.datetime2
, nconnectz.item_id
, nconnectz.vendor_id
, nconnectz.store
, nconnectz.sub_category
, nconnectz.itemandsize
, nconnectz.item
, nconnectz.pkg_quantity
, nconnectz.pkg_price
, nconnectz.price
FROM (
SELECT item_id
, pkg_quantity
, vendor_id
, MAX(datetime2) AS latest
FROM nconnectz
WHERE item_id IN (6,7)
AND vendor_id IN (2,5)
GROUP BY item_id , vendor_id, pkg_quantity
) AS subset
INNER JOIN nconnectz
ON nconnectz.item_id = subset.item_id
AND nconnectz.vendor_id = subset.vendor_id
AND nconnectz.datetime2 = subset.latest
AND nconnectz.pkg_quantity = subset.pkg_quantity
INNER JOIN (SELECT route_order, item, time_stamp FROM route_order WHERE user_id = 31) AS z
ON z.item = nconnectz.item
ORDER BY item_id ASC , vendor_id ASC, price ASC, pkg_price ASC
SELECT id
, z.route_order
, z.time_stamp
, nconnectz.datetime2
, nconnectz.item_id
, nconnectz.vendor_id
, nconnectz.store
, nconnectz.sub_category
, nconnectz.itemandsize
, nconnectz.item
, nconnectz.pkg_quantity
, nconnectz.pkg_price
, nconnectz.price
FROM (
SELECT item_id
, pkg_quantity
, vendor_id
, MAX(datetime2) AS latest
FROM nconnectz
WHERE item_id IN (6,7)
AND vendor_id IN (2,5)
GROUP BY item_id , vendor_id, pkg_quantity
) AS subset
INNER JOIN nconnectz
ON nconnectz.item_id = subset.item_id
AND nconnectz.vendor_id = subset.vendor_id
AND nconnectz.datetime2 = subset.latest
AND nconnectz.pkg_quantity = subset.pkg_quantity
INNER JOIN (SELECT route_order, item, time_stamp
FROM (
SELECT route_order, item, MAX(time_stamp) AS latest_ts FROM route_order WHERE user_id = 31
) AS z2
INNER JOIN route_order AS z
ON z.item = nconnectz.item
AND z.time_stamp = z2.latest_ts
ORDER BY item_id ASC , vendor_id ASC, price ASC, pkg_price ASC
Also, have an open paren that I don’t know how to resolve. Deleting it doesn’t help.
Current code:
SELECT id
, z.route_order
, z.time_stamp
, nconnectz.datetime2
, nconnectz.item_id
, nconnectz.vendor_id
, nconnectz.store
, nconnectz.sub_category
, nconnectz.itemandsize
, nconnectz.item
, nconnectz.pkg_quantity
, nconnectz.pkg_price
, nconnectz.price
FROM (
SELECT item_id
, pkg_quantity
, vendor_id
, MAX(datetime2) AS latest
FROM nconnectz
WHERE item_id IN (6,7)
AND vendor_id IN (2,5)
GROUP BY item_id , vendor_id, pkg_quantity
) AS subset
INNER JOIN nconnectz
ON nconnectz.item_id = subset.item_id
AND nconnectz.vendor_id = subset.vendor_id
AND nconnectz.datetime2 = subset.latest
AND nconnectz.pkg_quantity = subset.pkg_quantity
INNER JOIN (SELECT route_order, item, time_stamp
FROM (
SELECT route_order, item, MAX(time_stamp) AS latest_ts FROM route_order WHERE user_id = 31
GROUP BY route_order, item) AS z2
INNER JOIN route_order AS z
ON z.item = nconnectz.item
AND z.time_stamp = z2.latest_ts
ORDER BY item_id ASC , vendor_id ASC, price ASC, pkg_price ASC
SELECT ...
FROM table1
INNER
JOIN table2 /* SEE NOTE 1 */
ON table2.something = table1.something
INNER
JOIN table3
ON table3.someotherthing = /* SEE NOTE 2 */
INNER
JOIN table4
ON table4.somethingelse = /* etc. */
note 1 – each table (including table 1) can actually be a derived table, i.e. a subquery in parentheses given a table alias
note 2 – always join on a column of a previously mentioned table, so in this example, table 3 could be joined based on column(s) from table 2 or table 1 or both
I think this follows the template though I get this error: Unknown column ‘z.latest_ts’ in ‘field list’
Where’s my variation from the template?
(also found the trick to make my indent stick)
SELECT id
, z.route_order
, z.latest_ts
, nconnectz.datetime2
, nconnectz.item_id
, nconnectz.vendor_id
, nconnectz.store
, nconnectz.sub_category
, nconnectz.itemandsize
, nconnectz.item
, nconnectz.pkg_quantity
, nconnectz.pkg_price
, nconnectz.price
FROM (
SELECT item_id
, pkg_quantity
, vendor_id
, MAX(datetime2) AS latest
FROM nconnectz
WHERE item_id IN (6,7)
AND vendor_id IN (2,5)
GROUP BY item_id , vendor_id, pkg_quantity
) AS subset
INNER
JOIN nconnectz
ON nconnectz.item_id = subset.item_id
AND nconnectz.vendor_id = subset.vendor_id
AND nconnectz.datetime2 = subset.latest
AND nconnectz.pkg_quantity = subset.pkg_quantity
INNER
JOIN (
SELECT route_order
, item
, time_stamp
FROM (
SELECT route_order
, item
, MAX(time_stamp) AS latest_ts
FROM route_order
WHERE user_id = 31
GROUP BY route_order
, item
) AS z2
INNER
JOIN
route_order
ON
route_order.time_stamp = z2.latest_ts
GROUP BY
route_order.route_order, z.item
) AS z
ON
z.item = nconnectz.item
SELECT id
, z3.route_order
, z3.time_stamp
, nconnectz.datetime2
, nconnectz.item_id
, nconnectz.vendor_id
, nconnectz.store
, nconnectz.sub_category
, nconnectz.itemandsize
, nconnectz.item
, nconnectz.pkg_quantity
, nconnectz.pkg_price
, nconnectz.price
FROM (
SELECT item_id
, pkg_quantity
, vendor_id
, MAX(datetime2) AS latest
FROM nconnectz
WHERE item_id IN (6,7)
AND vendor_id IN (2,5)
GROUP BY item_id , vendor_id, pkg_quantity
) AS subset
INNER
JOIN nconnectz
ON nconnectz.item_id = subset.item_id
AND nconnectz.vendor_id = subset.vendor_id
AND nconnectz.datetime2 = subset.latest
AND nconnectz.pkg_quantity = subset.pkg_quantity
INNER
JOIN (
SELECT z.route_order
, z.item
, z.time_stamp
FROM (
SELECT route_order
, item
, MAX(time_stamp) AS latest_ts
FROM route_order
WHERE user_id = 31
GROUP BY route_order
, item
) AS z2
INNER
JOIN
route_order AS z
ON
z.time_stamp = z2.latest_ts
GROUP BY
z.route_order, z.item
) AS z3
ON
z3.item = nconnectz.item
(
SELECT route_order
, item
, MAX(time_stamp) AS latest_ts
FROM route_order
WHERE user_id = 31
GROUP BY route_order
, item
) AS z2
INNER
JOIN
route_order AS z
ON
z.time_stamp = z2.latest_ts
it will run, but it joins route_order to only the latest timestamp, ragardless of whether it’s for the correct route order or item
note the GROUP BY – there are multiple values for latest_ts
if all you want is only one latest_ts overall, you don’t really need the subquery
Great catch! I was so thrilled that it worked, that I missed that.
I wonder how long I would’ve missed that? After all, that was the point of selecting route order and item as additional selects.
Top-of-the-mind-reminder: The possibility that it works, but not the way I think it does.
New subquery:
(
SELECT z.route_order
, z.item
, z.time_stamp
FROM (
SELECT route_order
, item
, MAX(time_stamp) AS latest_ts
FROM route_order
WHERE user_id = 31
GROUP BY route_order
, item
) AS z2
INNER
JOIN
route_order AS z
ON z.time_stamp = z2.latest_ts
#additional conditions
AND z.item = z2.item
AND z.route_order = z2.route_order
#additional conditions
GROUP BY
z.route_order, z.item
)