mysql - SQL code assistance for finding latest record based on ID

Question

This is a question off the back of my older question as it was unclear as to what i was looking for. Old questions can be found here: need to know if this is possible in SQL

I have sales orders with quotes against them however new quote revisions are made and sometimes the sales orders don't get updated. So i need to write a query to find any orders that don't have the most updated quote.

Currently this is done in excel but i want to be able to run a query to get the same results.

MY SQL or SQL Server.

Table and fields Sales order lines - sales order ID / current quote ID Quotes - Quote ID

The sales order ID is a numerical field XXXXX. The quote ID fields are like 12345 and if a new revision is created it a new quote is added and called 12345-A it goes up alphabetically per revision.

Example data

Sales Orders

omlSalesOrderID | omlQuoteID
53849           | 12345
31486           | 95648-A
12489           | 68745-D
68732           | 32149-E

Quotes

qmpQuoteID
12345   
12345-A 
12345-B 
95648   
95648-A 
95648-B 
95648-C 
68745   
68745-A 
68745-B 
68745-C 
68745-D 
32149   
32149-A 
32149-B 
32149-C 
32149-D 
32149-E 

What i want to see is

Sales order ID | Current Quote ID | Latest Quote ID
---------------|------------------|-----------------
53849          | 12345            | 12345-B
31486          | 95648-A          | 95648-C
12489          | 68745-D          | 68745-D
68732          | 32149-E          | 32149-E

Answer 1

In MySQL, you can use substring_index() and aggregation:

select o.quoteId, o.salesorderid,
       max(q.quote_id)
from orders o left join
     quotes q
     on o.quoteId = substring_index(q.quoteId, '-', 1)
group by o.quoteId;

In SQL Server (or MySQL as well), you can use LIKE for the comparison:

select o.quoteId, o.salesorderid,
       max(q.quote_id)
from orders o left join
     quotes q
     on q.quoteId like concat(o.quoteId, '-%')
group by o.quoteId;