Post reply

''First'' not supported in SQL Server

  • September 13, 2007 at 11:36 pm

    #178347

    I am using following query in access

    Select first(fname),age from mytab group by age

      Database   :

     

      Sam     21

      Raj      21

      Karan   21

      John    23

      Rocky  23

      O/P of Query :

      Sam   21

      John   23       

    I want to use the same query in SQL Server 2005,But First keyword is not supported in SQL Server 2005.So how can I write my own function  'first' in SQL Server 2005, so that I will not need to change above query in SQL Server also and it should give same result.

  • September 14, 2007 at 12:44 am

    #733673

    Have you tried using

    TOP

    or

    PERENT

     

    Thanks

    Chris

    ----------------------------------------------
    Try to learn something about everything and everything about something. - Thomas Henry Huxley

    :w00t:
    Posting Best Practices[/url]
    Numbers / Tally Tables[/url]

    SQL-4-Life

  • September 14, 2007 at 1:22 am

    #733677

    SELECT Name, Age FROM (

    SELECT Name, Age, ROW_NUMBER() OVER (PARTITION BY Age ORDER BY Name) AS RecID FROM MyTab

    ) AS d WHERE RecID = 1

    SELECT Age, MIN(Name) FROM MyTab GROUP BY Age

     

    N 56°04'39.16"
    E 12°55'05.25"

  • September 15, 2007 at 1:34 pm

    #733957

    In your Access query, FIRST just retrieves a value of the name column from some random row. Assuming you don't actually require a random result but don't mind which name is returned just substitute MIN or MAX in place of FIRST in your query.

  • September 15, 2007 at 4:58 pm

    #733963

     SELECT MIN(fName),Age

       FROM yourtable

      GROUP BY Age

    Although not sure why you want to give the alphabetically earlier names such a break.  Why do you need to do this?

    --Jeff Moden

    RBAR is pronounced "ree-bar" and is a "Modenism" for Row-By-Agonizing-Row.
    First step towards the paradigm shift of writing Set Based code:
    ________Stop thinking about what you want to do to a ROW... think, instead, of what you want to do to a COLUMN.
    "Change is inevitable... change for the better is not".
    Helpful Links:
    How to post code problems
    How to Post Performance Problems
    Create a Tally Function (fnTally)
    Intro to Tally Tables and Functions

  • September 17, 2007 at 7:56 am

    #734060

    MSAccess was where I ran into First() as an aggregate function.  It was at first glance a bit useless, but it was actually used to help make your queries run a little faster by keeping the GROUP BY field list small.  The idea was - if you could keep the GROUP BY to fields that were indexed - it would do its job better.

    An example might be better:  Suppose you have an employee table, and an absences table (with dates and reasons).  Then a report to assess how many times you'd been absent by reason code might be:

    Select e.employeeID, first(e.Firstname),first(e.Lastname), a.reason, count(a.date)

    from employeelist e inner join absences a on e.employeeid=a.employeeid

    group by e.employeeID, a.reason

     

    The firstname/lastname fields don't "add" any value to the GROUP BY, so they just "complicate" matters by being there.

    ----------------------------------------------------------------------------------
    Your lack of planning does not constitute an emergency on my part...unless you're my manager...or a director and above...or a really loud-spoken end-user..All right - what was my emergency again?

Viewing 6 posts - 1 through 5 (of 5 total)

You must be logged in to reply to this topic. Login to reply