Tsql Left Join and Only Last Row from Right

TSQL left join and only last row from right

SELECT  post.id, post.title, comment.id, comment.message
FROM    post
OUTER APPLY
        (
        SELECT  TOP 1 *
        FROM    comment с
        WHERE   c.post_id = post.id
        ORDER BY
                date DESC
        ) comment

or

SELECT  *
FROM    (
        SELECT  post.id, post.title, comment.id, comment.message,
                ROW_NUMBER() OVER (PARTITION BY post.id ORDER BY comment.date DESC) AS rn
        FROM    post
        LEFT JOIN
                comment
        ON      comment.post_id = post.id
        ) q
WHERE   rn = 1

The former is more efficient for few posts with many comments in each; the latter is more efficient for many posts with few comments in each.

left join and return only the last record from right table

The JOIN condition for your join to second_table is incorrect; you need to join on both payment_instrument_id and id to get only the last row:

select p_i.id, p_i.user_id,  a_r.id, a_r.payment_instrument_id 
from payment_instruments p_i
left join (
  select a_r.payment_instrument_id, max(a_r.id) id
  from second_table a_r
  group by a_r.payment_instrument_id
) lastSD on lastSD.payment_instrument_id = p_i.id
left join second_table a_r on a_r.payment_instrument_id = lastSD.payment_instrument_id and a_r.id = lastSD.id  
where p_i.user_id = 1030

Output (for a truncated sample of your data):

id      user_id     id  payment_instrument_id
803     1030        86  803
958     1030        

Note you can simplify your code with a CTE using ROW_NUMBER():

WITH a_r AS (
  SELECT id,
         payment_instrument_id,
         ROW_NUMBER() OVER (PARTITION BY payment_instrument_id ORDER BY id DESC) AS rn
  FROM second_table
)
SELECT p_i.id, p_i.user_id, a_r.id, a_r.payment_instrument_id 
FROM payment_instruments p_i
LEFT JOIN a_r ON a_r.payment_instrument_id = p_i.id AND a_r.rn = 1

The output is the same as the first query.

Demo (of both queries) on dbfiddle

MySQL – left join on last record in the right table

You need a subquery that returns the last message for each channel and joined to table1 and then join table3 twice:

select t1.*, t31.firmenname, t32.firmenname firmenname2, t2.nachricht 
from table1 t1 
left join (
  select t.* from table2 t
  where not exists (
    select 1 from table2
    where channel = t.channel and datum > t.datum
  )
) t2 on t2.channel = t1.id
left join table3 t31 on t31.id = t1.id1
left join table3 t32 on t32.id = t1.id2
order by t1.id

See the demo.

Results:

| id  | id1 | id2 | g_id1 | g_id2 | datum     | firmenname   | firmenname2  | nachricht    |
| --- | --- | --- | ----- | ----- | --------- | ------------ | ------------ | ------------ |
| 1   | 1   | 2   | x     | x     | timestamp | companyname1 | companyname2 | messagetext2 |
| 2   | 3   | 1   | x     | x     | timestamp | companyname3 | companyname1 | messagetext4 |
| 3   | 3   | 2   | x     | x     | timestamp | companyname3 | companyname2 | messagetext5 |

SQL join: selecting the last records in a one-to-many relationship

This is an example of the greatest-n-per-group problem that has appeared regularly on StackOverflow.

Here's how I usually recommend solving it:

SELECT c.*, p1.*
FROM customer c
JOIN purchase p1 ON (c.id = p1.customer_id)
LEFT OUTER JOIN purchase p2 ON (c.id = p2.customer_id AND 
    (p1.date < p2.date OR (p1.date = p2.date AND p1.id < p2.id)))
WHERE p2.id IS NULL;

Explanation: given a row p1, there should be no row p2 with the same customer and a later date (or in the case of ties, a later id). When we find that to be true, then p1 is the most recent purchase for that customer.

Regarding indexes, I'd create a compound index in purchase over the columns (customer_id, date, id). That may allow the outer join to be done using a covering index. Be sure to test on your platform, because optimization is implementation-dependent. Use the features of your RDBMS to analyze the optimization plan. E.g. EXPLAIN on MySQL.

---

Some people use subqueries instead of the solution I show above, but I find my solution makes it easier to resolve ties.

Access - LEFT JOIN Query Most Recent Record Only

You can get the most recent row first before joining:

SELECT o.OpportunityID, n.NotesDate, n.NotesID
FROM Opportunity as o LEFT JOIN
     (SELECT n.*
      FROM tblNotes as n
      WHERE n.NotesDate = (SELECT MAX(n2.NotesDate)
                           FROM tblNotes as n2
                           WHERE n2.OpportunityID = n.OpportunityID
                          )
     ) as n
     ON o.OpportunityID = n.opportunityid 
ORDER BY o.OpportunityID;

Most recent record in a left join

Try this:

SELECT a.State, count(c.CustomerID)
FROM Product p
INNER JOIN Customer c ON c.CustomerID = p.CustomerID
LEFT JOIN Address a ON a.CustomerID = c.CustomerID 
      AND a.AddressID = 
        (
           SELECT MAX(AddressID) 
           FROM Address z 
           WHERE z.CustomerID = a.CustomerID
        )
WHERE p.ProductID = 101
GROUP BY a.State

SQL Left join: selecting the last records in a one-to-many relationship

You got it almost right.

Your first query removes all customers that don't have details with the specified product, because you didn't specifiy the product filter in the ON condition of the first OUTER JOIN.

SELECT
    cust.Customer
  , cust.Company
  , inv.Date
  , inv.Product
  , inv.Units
  , inv.Extended 
FROM
  customerlist cust
LEFT OUTER JOIN 
  detail inv 
ON 
      cust.customer = inv.customer
  AND inv.Product IN ('CC', 'CG', 'CH')
LEFT OUTER JOIN 
  detail inv2 
ON 
      inv.customer = inv2.customer 
  AND (
          inv.date < inv2.date 
       OR inv.date = inv2.date AND inv.customer < inv2.customer
      )
WHERE 
  inv2.customer IS NULL

That should do it.

There is one other thing I think is not quite correct. The AND inv.customer < inv2.customer part should probably be AND inv.id < inv2.id (if there is an id field in the detail table).

That's because the OR condition is filtering the detail records that have the same date by their primary key.

UPDATE

Since the table in question has no primary key field you can use the ROWID ADS feature to solve that:

SELECT
    cust.Customer
  , cust.Company
  , inv.Date
  , inv.Product
  , inv.Units
  , inv.Extended 
FROM
  customerlist cust
LEFT OUTER JOIN 
  detail inv 
ON 
      cust.customer = inv.customer
  AND inv.Product IN ('CC', 'CG', 'CH')
LEFT OUTER JOIN 
  detail inv2 
ON 
      inv.customer = inv2.customer 
  AND (
          inv.date < inv2.date 
       OR inv.date = inv2.date AND inv.ROWID < inv2.ROWID
      )
WHERE 
  inv2.customer IS NULL

Forcing left join to only return one row from matching Ids in the right table

One method uses row_number():

Select F.*, B.*
from Foo f left join
     (select b.*, row_number() over (partition by b.id order by id) as seqnum
      from bar b
     ) b
     on f.Id = B.Id and seqnum = 1; 

The order by specifies what you mean by "first". The order by id is an arbitrary ordering.

And alternative method uses outer apply:

Select F.*, B.*
from Foo f outer apply
     (select top 1 b.*
      from bar b
      where f.Id = B.Id
     ) b; 

In this case, you would add an order by to the subquery to get the "first" based on some column. Also, this should have better performance than the previous version.

---

Related Topics