need TOP record value in a FUNCTION

[TheBlueDog]

I am trying to get a single integer value (primary key/identity val from a table) returned in a function. This function queries a table to find the 1 or more records which match criteria I pass in, and the key value I need returned is the key from the very first record in the recordset as I sort it in a special sort order. The problem is I seem to only be able to get the LAST value out of the table assigned to my variable, and never the first.

Here is my example:
Table = MyTable
Fields = CID (the value I need returned), Repid Int, Inventoryid Int, Locationid Int
Return value = @cidreturned int
variables passed in to function: @inventoryid, @locationid, @repid

In my function I do this:

select @cidreturned = cid from mytable where (inventoryid = @inventoryid or repid = @repid or locationid = @locationid) order by inventoryid desc,repid desc, locationid desc

If multiple records match this query the value I want is the first cid, but instead I can only get the last one assigned as my return value.

I saw an earlier similar post about using TOP 1, which just as a query works fine but I cant get to work in my function, where I also can't use a cursor or temp table.

Anyone have any ideas?!? Thank you!

 

[swampBoogie]

There is no concept of first or last within a table.

You can get the cid with the highest or lowest value.

select @cidreturned = min(cid) from mytable where (inventoryid = @inventoryid or repid = @repid or locationid = @locationid)

 

[ESquared]

Change your desc sort orders to asc

-------------------------------------
There are 10 kinds of people in the world: those who know binary, and those who don't.