Results 1 to 3 of 3

Thread: First() to SQL?

  1. #1
    Join Date
    Jul 2003

    Unanswered: First() to SQL?


    I'm working with a project translating Access databases to SQL Server.

    Can anyone explain the mystic function First() to me?

    How can it's function be replaced by SQL?

    (I've posted this in the Accessforum also)

  2. #2
    Join Date
    Jan 2003
    Provided Answers: 11
    As near as I remember the First() function is used as a sort of "get out of jail free card" for group by situations. Instead of grouping by the value in the column, or summing up the column, or getting a max or min of the column, Access grabs the first value it sees. Because of this, you can end up with different results in different situations, which is generally bad for business. Here is a link to some of the help I found..

    In SQL Server, I would avoid using the concept of "first" as it does not really have any meaning, unless you impose a meaning like "chronologically first entered", in which case you would (hopefully) have an entered date to work with. Hope this helps.

  3. #3
    Join Date
    Jun 2003
    Provided Answers: 1
    First() and Last automatically go to either the first or last record in your dataset (presumably sorted) and returns the field you specify.

    In SQL you will need to do this in two stages. First, find the Primary Key value of the First or Last record, and then look up the value of the field in the record associated with that key.

    select [YourValue] as FirstValue
    from [YourTable]
    inner join
    (select min([SortKey]) as FirstKey from [YourTable]) Subquery
    where [YourTable].[SortKey] = Subquery.FirstKey

    If you sortkey is not unique, you will get multiple records in your result.
    If it's not practically useful, then it's practically useless.

    blindman "sqlblindman"

Posting Permissions

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