Professional Web Applications Themes

First () Function - Microsoft SQL / MS SQL Server

We are in the process of converting our Access 97 Database to SQL 2000. We will be using a Access 2002 front end. We are in the process of rewriting our DAO code to ADO. I am sure this has been asked a thousand times, but how can you create a User defined function to do the equivalent of the First() function ?...

  1. #1

    Default First () Function

    We are in the process of converting our Access 97
    Database to SQL 2000. We will be using a Access 2002
    front end. We are in the process of rewriting our DAO
    code to ADO. I am sure this has been asked a thousand
    times, but how can you create a User defined function to
    do the equivalent of the First() function ?
    Greg Guest

  2. #2

    Default Re: First () Function

    SELECT * FROM tableName ORDER BY colName
    If you want last():
    SELECT * FROM tableName ORDER BY colName DESC

    You need to order the result set because SQL server has no concept of First and Last.
    By definition, a table is an unordered set of rows.

    "Greg" <GMaceclaimline.com> a écrit dans le message de news: 03ce01c340d2$a049d950$a301280aphx.gbl...
    > We are in the process of converting our Access 97
    > Database to SQL 2000. We will be using a Access 2002
    > front end. We are in the process of rewriting our DAO
    > code to ADO. I am sure this has been asked a thousand
    > times, but how can you create a User defined function to
    > do the equivalent of the First() function ?
    Guest

  3. #3

    Default Re: First () Function

    Oh do I feel stupid.
    Thanks Dinesh for kindly pointing that out. :-(

    That's:
    SELECT TOP 1 * FROM tableName ORDER BY colName

    "Dinesh.T.K" <tkdineshnospam.mail.tkdinesh.com> a écrit dans le message de news: [email]em7C6QNQDHA.2480tk2msftngp13.phx.gbl[/email]...
    > Raydan,
    >
    > Did you miss mentioning TOP ?
    >
    > --
    > Dinesh.
    > SQL Server FAQ at
    > [url]http://www.tkdinesh.com[/url]
    >
    > <raydan> wrote in message news:uYe57NNQDHA.3088TK2MSFTNGP10.phx.gbl...
    > SELECT * FROM tableName ORDER BY colName
    > If you want last():
    > SELECT * FROM tableName ORDER BY colName DESC
    >
    > You need to order the result set because SQL server has no concept of First
    > and Last.
    > By definition, a table is an unordered set of rows.
    >
    > "Greg" <GMaceclaimline.com> a écrit dans le message de news:
    > 03ce01c340d2$a049d950$a301280aphx.gbl...
    > > We are in the process of converting our Access 97
    > > Database to SQL 2000. We will be using a Access 2002
    > > front end. We are in the process of rewriting our DAO
    > > code to ADO. I am sure this has been asked a thousand
    > > times, but how can you create a User defined function to
    > > do the equivalent of the First() function ?
    >
    >
    Guest

  4. #4

    Default Re: First () Function

    There is no concept of FIRST() in SQL Server, since rows are not stored in
    any determinable physical order.

    Maybe you mean:

    SELECT TOP 1 column_list FROM table ORDER BY some_order_preference




    "Greg" <GMaceclaimline.com> wrote in message
    news:03ce01c340d2$a049d950$a301280aphx.gbl...
    > We are in the process of converting our Access 97
    > Database to SQL 2000. We will be using a Access 2002
    > front end. We are in the process of rewriting our DAO
    > code to ADO. I am sure this has been asked a thousand
    > times, but how can you create a User defined function to
    > do the equivalent of the First() function ?

    Aaron Bertrand - MVP Guest

  5. #5

    Default Re: First () Function

    "Aaron Bertrand - MVP" <aaronTRASHaspfaq.com> wrote in message
    news:%23pvjcjNQDHA.1556TK2MSFTNGP10.phx.gbl...
    > There is no concept of FIRST() in SQL Server, since rows are not stored in
    > any determinable physical order.
    >
    > Maybe you mean:
    >
    > SELECT TOP 1 column_list FROM table ORDER BY some_order_preference
    >
    >

    First() is a handy aggreagate and its functionality cannot be completely
    reproduced with TOP values queries, because TOP values queries don't respect
    group by's.

    For instance with First you could write something like:

    <pseudocode>
    select
    o.CustomerID,
    count(*) orders,
    First(ShipName) FirstOrderShipName,
    First(ShipVia) FirstOrderShipVia
    from orders
    group by o.customerid
    order by o.customerid, o.orderdate
    </pseudocode>

    To return results grouped by customer, and add some information about the
    customer's first order. The kicker is, of course, that to make this work
    you would have to apply the "order by" before the "group by", and allow
    columns in the "order by" that don't appear in the "group by". Or rely on
    the an underlying ordering of the rows. Which is inconsistent with the
    design of SQL and of large RDBMS's.

    The good thing about this is that the query is simple and the data is
    returned in one pass.


    To get the same result in TSQL, you have to write something like:

    select
    o.customerID,
    count(*) orders,
    min(first_order.ShipName) FirstOrderShipName,
    min(first_order.ShipVia) FirstOrderShipVia
    from orders o
    join orders first_order
    on first_order.orderid = (select top 1 orderid from orders
    where customerid = o.customerid
    order by orderdate)
    group by o.customerid
    order by o.customerid

    Which is as expensive as it is ugly. But it does answer the poster's
    question.


    David


    David Browne Guest

Similar Threads

  1. Replies: 3
    Last Post: March 19th, 07:59 PM
  2. Replies: 0
    Last Post: October 24th, 12:01 AM
  3. Replies: 0
    Last Post: October 14th, 04:28 PM
  4. note 33575 added to function.register-shutdown-function
    By jules@sitepointAASASZZ.com in forum PHP Notes
    Replies: 0
    Last Post: July 1st, 05:41 AM
  5. Replies: 0
    Last Post: July 1st, 12:31 AM

Bookmarks

Posting Permissions

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

1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 17 18 19 20 21 22 23 24 25 26 27 28 29 30 31 32 33 34 35 36 37 38 39 40 41 42 43 44 45 46 47 48 49 50 51 52 53 54 55 56 57 58 59 60 61 62 63 64 65 66 67 68 69 70 71 72 73 74 75 76 77 78 79 80 81 82 83 84 85 86 87 88 89 90 91 92 93 94 95 96 97 98 99 100 101 102 103 104 105 106 107 108 109 110 111 112 113 114 115 116 117 118 119 120 121 122 123 124 125 126 127 128 129 130 131 132 133 134 135 136 137 138 139