Get the latest distinct column with a join?

Databases
1

Hello All,

I have a query which looks in two tables for data and performs a join and works great:


SELECT     
           DISTINCT dc.order_id, 
           dc.id,
           dc.campaign_name,
           dc.campaign_start_date,
           dc.campaign_end_date,
           dc.salesperson,
           dc.ad_type,
           dws.order_id, 
           dws.impressions,
           dws.clicks
         FROM 
           cart_campaigns AS dc
         INNER JOIN 
           cart_weekly_stats AS dws ON 
           dc.order_id = dws.order_id AND dc.campaign_start_date = 
         (SELECT MAX(campaign_start_date) FROM dart_weekly_stats WHERE order_id = dc.order_id)
	      WHERE dc.salesperson  = '10'
        ORDER BY 
        dc.order_id DESC

However, I just have one problem I simply want the result to output the LATEST row for each order_id, when as you can see in the below it outputs all the results, so I just want the highlighted latest ones returned that are both different order id’s (the latest ones for that particular order_id)?

Can anyone help. Tried on another forum but to no avail. Any experts here who could kind help?

Thanks

2 
SELECT dc.order_id
     , dc.id 
     , dc.site_id
     , dc.campaign_name 
     , dc.campaign_start_date 
     , dc.campaign_end_date 
     , dc.salesperson 
     , dc.ad_type 
     , dws.order_id 
     , dws.impressions 
     , dws.clicks
     , dws.site_id 
  FROM cart_campaigns AS dc 
INNER 
  JOIN ( SELECT order_id 
              , site_id
              , MAX(id) AS max_id 
           FROM cart_weekly_stats 
         GROUP
             BY order_id 
              , site_id ) AS m 
    ON m.order_id = dc.order_id
   AND m.site_id = dc.site_id
INNER 
  JOIN cart_weekly_stats AS dws 
    ON dws.order_id = m.order_id 
   AND dws.id = m.max_id  
   AND dws.site_id = m.site_id
 WHERE dc.salesperson = '10'  
ORDER 
    BY dc.order_id DESC
3

Hi,

As I said before thanks for your help, but wondering if I could ask one more question please as i’m totally stuck again. Here’s my changed query now:


SELECT dc.order_id , 
       dc.id , 
	   dc.site_id, 
	   dc.campaign_name , 
	   dc.campaign_start_date , 
	   dc.campaign_end_date , 
	   dc.salesperson , 
	   dc.ad_type , 
	   dws.order_id , 
	   dws.impressions , 
	   dws.clicks,
	   dws.site_id 
FROM cart_campaigns AS dc 
INNER JOIN ( SELECT order_id , MAX(id) AS max_id FROM cart_weekly_stats WHERE site_id = 'the result from dc.site_id' GROUP BY order_id ) AS m 
ON m.order_id = dc.order_id
INNER JOIN cart_weekly_stats AS dws 
ON dws.order_id = m.order_id AND dws.id = m.max_id  AND dc.site_id = dws.site_id
WHERE dc.salesperson = '10'  
ORDER BY dc.order_id DESC

Now my problem is with the inner join


INNER JOIN ( SELECT order_id , MAX(id) AS max_id FROM cart_weekly_stats WHERE site_id = 'the result from dc.site_id' GROUP BY order_id ) AS m

Basically what I want to do here is say where site_id is equal to the value from the result of the opening query for dc.site_id here

ie


SELECT  dc.site_id FROM cart_campaigns AS dc

Is there any way I can do that?

Thanks

4

Sorry there should not be multiple rows. This helps me though nicely. Thanks very much for your help :slight_smile:

5

i know what you want the query to do, i was trying to get you to see the problem :slight_smile:

we could use the id column to distinguish the “latest” row but the more important issue is why there are multiple rows for the same period

SELECT dc.order_id
     , dc.id
     , dc.campaign_name
     , dc.campaign_start_date
     , dc.campaign_end_date
     , dc.salesperson
     , dc.ad_type
     , dws.order_id
     , dws.impressions
     , dws.clicks
  FROM cart_campaigns AS dc
INNER 
  JOIN ( SELECT order_id
              , MAX(id) AS max_id 
           FROM cart_weekly_stats 
         GROUP
             BY order_id ) AS m
    ON m.order_id = dc.order_id 
INNER
  JOIN cart_weekly_stats AS dws 
    ON dws.order_id = m.order_id 
   AND dws.id = m.max_id
 WHERE dc.salesperson  = '10'
ORDER 
    BY dc.order_id DESC
6

Hi,

Yes that’s what i’m not sure how to do. I know the dates are the same, but I just want the latest record for each order_id.

So I have my table cart_orders

Which holds disinct orders And as you can see it has two records in it with different order_id

Now what I want to do is do a query so that cart_orders joins with a table called cart_weekly_stats

Now each week an orders impressions and clicks are updated with a new row in the cart_weekly_stats table as shown here:

So what i’m aiming to do grab each individual order_id from cart_orders and then query the cart_weekly_stats table to get the LATEST stats for each order_id with the join. So the blue records below should only be outputted

Thanks again

7

what is the relationship between the cart_weekly_stats and dart_weekly_stats tables?

8

and how do you propose to select the “latest” campaign if more than one campaign has the same campaign_start_date?

9

Thanks for helping. Sorry that should also be cart_weekly_stats. Typo just in the above.