CODEWITHSUNDEEP
sql-servert-sqlstored-proceduresaggregate-functions
LYKS
LYKS

Reputation: 69

Selecting the highest ID number in an SQL stored proc

I am writing a stored procedure that looks like this:

SELECT Replace(c_invoice, Char(13) + Char(10), ''),
       c_uom1,
       c_uom2
FROM   @invoice_TABLE AS invoice
       INNER JOIN @uom1_TABLE AS uom1
               ON invoice.ID = uom1.ID
       INNER JOIN @uom2_TABLE AS uom2
               ON uom1.ID = uom2.ID
WHERE  uom1.ID = 4

But I would like the WHERE clause to use the highest uom1.ID number - can someone show me how to do this?

Upvotes: 1

Views: 154

Answers (3)

Chamal
Chamal

Reputation: 1449

Try this,

SELECT replace(c_invoice,char(13) + char(10), ''), c_uom1, c_uom2 FROM @invoice_TABLE AS invoice
    INNER JOIN (select MAX(ID) AS ID FROM @uom1_TABLE) as uom1 ON invoice.ID = uom1.ID
    INNER JOIN @uom2_TABLE as uom2 ON uom1.ID = uom2.ID

Upvotes: 0

valex
valex

Reputation: 24144

SELECT replace(c_invoice,char(13) + char(10), ''), c_uom1, c_uom2 
    FROM @invoice_TABLE AS invoice
    INNER JOIN @uom1_TABLE as uom1 ON invoice.ID = uom1.ID
    INNER JOIN @uom2_TABLE as uom2 ON uom1.ID = uom2.ID
    WHERE uom1.ID = (SELECT MAX(ID) FROM @uom1_TABLE)

Upvotes: 0

Martin Smith
Martin Smith

Reputation: 453426

Instead of WHERE you can use TOP WITH TIES... ORDER BY

SELECT TOP (1)  WITH TIES replace(c_invoice, char(13) + char(10), ''),
               c_uom1,
               c_uom2
FROM   @invoice_TABLE AS invoice
       INNER JOIN @uom1_TABLE AS uom1
         ON invoice.ID = uom1.ID
       INNER JOIN @uom2_TABLE AS uom2
         ON uom1.ID = uom2.ID
ORDER  BY uom1.ID DESC

Upvotes: 1

Related Questions