CODEWITHSUNDEEP
sql-serversql-server-2008stored-procedures
user3238418
user3238418

Reputation: 9

SQL Server 2008 Stored Procedure Select column from last row in table using ORDER BY column DESC

New to stored procedures. I have a table with the following two columns

ID (identity)
NoPro = CCNNN  where CC = Country Code and NNN sequential number within country

Data:

1   AR101
2   AR102
3   AR103
4   BO101
5   BO102
6   BR101
7   BR102

In the stored procedure I want to access the last project for a specific country in order to assign the next project number for that country.

I was trying the following code:

select @sNo = [NoPro] 
from projects 
where substring([NoPro], 1, 2) = @Country 
order by NoPro DESC

If I use AR for country I get AR101 while I was expecting AR103.

What am I missing?

Thanks!

Upvotes: 0

Views: 931

Answers (2)

Gordon Linoff
Gordon Linoff

Reputation: 1270081

Another way to solve this is to use top:

select top 1 @sNo = [NoPro] 
from projects 
where substring([NoPro], 1, 2) = @Country 
order by NoPro DESC;

Your query is returning multiple rows. Which gets assigned to @sNo is either arbitrary or the last value processed -- I'm not sure if SQL Server guarantees this. In any case, the solution is to fix the query to return one value.

Upvotes: 1

Mikael Eriksson
Mikael Eriksson

Reputation: 138970

Get the max NoPro value for your country should fix your issue and if you change the where clause to use like you might even use a index on NoPro to find the row you are looking for.

The design you have here is a bit troublesome when it comes to concurrency so please do at least create a unique constraint on NoPro to prevent duplicates.

select @sNo = max(NoPro)
from projects 
where NoPro like @Country+'%';

Upvotes: 1

Related Questions