Results 1 to 3 of 3

Thread: First() to SQL?

  1. #1
    Join Date
    Jul 2003
    Posts
    123

    Unanswered: First() to SQL?

    Hi,

    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
    Location
    Massachusetts
    Posts
    5,799
    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..

    http://office.microsoft.com/en-us/as...345631033.aspx

    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
    Location
    Ohio
    Posts
    12,592
    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
    www.chess.com: "sqlblindman"
    www.LobsterShot.blogspot.com

Posting Permissions

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