Post reply

Looking for advice on best way to execute query

  • October 12, 2007 at 9:23 am

    #182712

    I have a requirement to write a query that will return a records position in the query.

    For example, I have an employee database with 50,000 employees in it, the query is sorted by hire date.

    Let's assume for the purposes of this question, that all of the employees were hired on different dates.

    To find out where in the list a person is based on EmployeeID, so if I pass the query 1401 as an EmployeeID, it returns 13002 as in 13002 out of 50,000.

    Thanks for your help.

    Stephen

  • October 12, 2007 at 9:35 am

    #741648

    Stephen

    Use the ROW_NUMBER function. You'll find it in Books Online and there are some examples of how to use it in there as well.

    John

  • October 12, 2007 at 9:40 am

    #741653

    Have a look at the ROW_NUMBER() function.

    Something like this:

    with er as

    (select ROW_NUMBER() OVER (ORDER BY HireDate) as rn,

    HireDate,

    EmpId

    from EmployeeTable)

    select rn from er where EmpId = 1001

  • October 12, 2007 at 9:44 am

    #741658

    Sorry, I should have specifiied, I need a query that will work on both 2005 and 2000.

    Stephen

  • October 12, 2007 at 9:48 am

    #741661

    Replace the CTE with a temp table including an identity column. Insert into that with an order by and then query the temp table.

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

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