Need to select Nth row in table...

[bartee]

I need to select the nth row from a table and only that row.

I am doing this within a user defined function and therefore I don't think I can use temporary tables.

For example, I have:
ID -- Code -- Value
1 A 5
2 A 10
3 B 5
4 A 25

Given the table is ordered by ID, I want to retrieve only the row having the third Code "A". In this example, it would be the 4th row.

If anyone can provide some help, it's greatly appreciated.

Thanks in advance.

 

[gmmastros]

You do know that you can create a table variable within a UDF, and table variables can have an identity column.

Another way - Select the top 3 ordered ascending, and then select the top 1 ordered desc. Like so:

Select Top 1 *
From   (
       Select Top 3 * 
       From   [!]Table[/!]
       Where  Code = 'A'
       Order By Id [blue]ASC[/blue]
       ) A 
Order By ID [blue]DESC[/blue]
[/blue]

-George

Strong and bitter words indicate a weak cause. - Fortune cookie wisdom

 

[bartee]

Thanks alot for the feedback.

Using table variables? any special restrictions or good code samples.

I have not used them in functions before and was not aware of it.

Thanks again

 

[gmmastros]

Something like this....

Declare @Temp Table(RowId Integer Identity(1,1), id Integer, Code VarChar(1), Value Integer)

Insert
Into   @Temp(Id, Code, Value)
Select Id, Code, Value
From   Table
Where  Code = 'A'
Order By Id

-- At this point, you have a table variable 
-- with the data you care about.  This table
-- has an identity column that you can use.

-- If you want the 3rd record, then....
Select * from @Temp
Where  RowId = 3

Hope this helps.

-George

Strong and bitter words indicate a weak cause. - Fortune cookie wisdom

 

[bartee]

Thanks again.

Like you said, as long as I can use it within a UDF -- I think it will.

 

[gmmastros]

You can absolutely use table variables within UDF's. If you have any problems with it, it would be syntax, not functionality.

You may want to get comfortable using table variables because often times, performace with table variables is better than temp tables.

-George

Strong and bitter words indicate a weak cause. - Fortune cookie wisdom

 

[bartee]

Thank You Again.

Very Helpful.