CODEWITHSUNDEEP
sqlsql-server
user2059972
user2059972

Reputation: 151

SQL Server - Find last row that matches a specific value

I'm new to SQL and I'm having trouble creating a query to find the last entry in a table where one column contains part of a specific string. I have a database similar to:

Part Number | Description | Qty 
------------+-------------+-----
   MO1      | Big Motor   | 1 
   MO2      | Small Motor | 5   
   XYZ      | Garbage     | 1 
   BO1      | Big Bolt    | 1 
   BO2      | Small Bolt  | 2

I'm looking for a way to query any entry in which the "Part Number" starts with "MO" then return just the last entry ("MO2/Small Motor/5" in this case). The part numbers are always sequential, but there can be a lot of junk data in between clumps of properly formatted numbers. I'm much more used to working with Excel where I can "Find" a string by searching in reverse order, but I don't know how to do the equivalent in SQL.

Upvotes: 3

Views: 3687

Answers (3)

Neal_RSA
Neal_RSA

Reputation: 64

SELECT
    TOP 1 *
FROM
    {TableName}
WHERE
    [Part Number] LIKE '%MO%'
ORDER BY
    [Part Number] DESC

The part numbers are always sequential

Reverse the order by part number and select the first one

If you want 'Mo' to be a variable change to

DECLARE
    @PartSearch NVARCHAR(MAX) = **'Mo'**
SELECT
    TOP 1 *
FROM
    {TableName}
WHERE
    [Part Number] LIKE '%' + @PartSearch + '%'
ORDER BY
    [Part Number] DESC

http://sqlfiddle.com/#!6/9385d/3

Upvotes: 0

sgeddes
sgeddes

Reputation: 62831

Assuming you could have junk data in between MO and the numeric part number, then here's one option to order by the ending characters:

select top 1 *
from yourtable
where partnumber like 'mo%'
order by cast(right(partnumber, patindex('%[^0-9 ]%',reverse(partnumber)) - 1) as int) desc

Using reverse with patindex you can get the remaining numeric characters at the end of the string. Then right makes it easy with cast to order the results.

Upvotes: 0

Gordon Linoff
Gordon Linoff

Reputation: 1269463

If by "last" you mean the largest value starting with MO, then you can do:

select top 1 t.*
from t
where partnumber like 'MO%'
order by partnumber desc     -- or some other column that specifies the ordering

If the numbers could end up looking like MO101, then you can do:

order by len(partnumber) desc, partnumber desc

Upvotes: 3

Related Questions