Results 1 to 5 of 5
  1. #1
    Join Date
    Aug 2002

    Unanswered: User Functions - getting first record

    Here is my problem - I have a user defined function that needs the FIRST value from a specially sorted result set. When I do my set statement I get the LAST value in my result set. I can't figure out how in a SQL function to only get that first value (since I can't do a cursor or temp table).

    Example: My table has a keyid (created by identity value, the value I need)
    Also it has a vendorid, a repid, and an inventoryid. These tie out to other tables.

    If I sort my result set in a certain descending order, the first record's keyid is the value I need. Problem is that I am getting the last keyid instead of the first. I can't switch my sort though and get the right value, because of some well-placed zeros. Hard to explain.

    I guess what I want is something like:

    Select @keyid = FIRST keyid from sortedtable order by vendorid desc, inventoryid desc, repid asc

    It's the select FIRST that hangs me up. Can't find that function - does it or something like it exist that I can use from within my function? I'm sure it can be done, I'm just sure I don't know how... Thanks for your help. - K

  2. #2
    Join Date
    Dec 2002
    Do you mean:
    SELECT TOP 1 * FROM YourTable ORDER BY YourMethod


    Have you hugged your backup today?

  3. #3
    Join Date
    Aug 2002

    can't set value like that

    I thought that syntax would work also, and while as a simple select statement it returns me the correct row, I can't do this:

    select @mykey = top 1 ccid from testtable order by vendorid desc, inventoryid desc

    etc. I get an error on that syntax. But the value I need, the ccid, is correctly selected in the top 1 syntax if I run it as a straight select in a query window!

    thank you for your suggestion though. Any second ideas, or alternate ways to set my value with that top syntax?

  4. #4
    Join Date
    Feb 2004
    select top 1 @mykey = ccid from testtable order by vendorid desc, inventoryid desc ?

  5. #5
    Join Date
    Aug 2002
    You are my hero, thank you! I had the syntax out of order and thought I just couldn't use that TOP statement to set my variable. (I was doing
    select @myrow = top 1 ccid from ...)

    Thanks again for the fix, I'm back in business.

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts