Post reply

sqlquery

  • September 14, 2008 at 10:41 pm

    #871588

    WIth temp table and Row_Number()

    Row_Number() numbers the rows on the basis of the "order by.." as specified by the over clause.

    select Row_Number() over (order by id) as No,* into #temp from table_name

    select * from #temp where no=40

    But remember that both row_number() and temp tables are heavy on the database.

  • September 16, 2008 at 8:35 am

    #872424

    I am still interested to know how they know the 40th row is the one needed? What if someone else adds more rows to this table? Would the correct row still be the 40th or would it now be the 45th? It seems to me that selecting data based on the row number is a short-lived solution to getting the data needed, and I would recommend selecting the rows based on the contents of the columns in that table.

    If it is about percentiles or ranking, I would use the nested "Top 1... Top 40 percent" technique. (Note: I used percent here on the idea that the table will grow/change over time, as opposed to wanting exactly the 40th row).

    I also like GSquared's CTE solution.

Viewing 2 posts - 16 through 16 (of 16 total)

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