How to Retrieve only nth row as a result by writing one query.

How to retrieve only nth row as a result of execution of one query?
For example:
Lte the table be:

SNo SudentName Marks
001 Ashok kumar 67
002 Anderson 70
003 Alfred 60
004 Ameeruddin 65
005 KalyanKumar. 69

Now the Query is: How is the 3rd ranker. The answer must be Ashok kumar.

How to write this query in SQL Server.

Query part 1: Select the top three records and ordered by marks (ASC)

Query part 2: select all records where the marks value is the MAX value of the first query part

Do you need to take into consideration, any tied value (e.g. two at 71) - how does this affect those with a score of 70?

|||

SELECT TOP 1 *
FROM (SELECT TOP 3 * FROM MyTable ORDER BY Marks DESC) t1
ORDER BY Marks ASC

|||A nice answer, if I may say|||It's a start, but he still has to deal with what to do with duplicates as you previously mentioned.

How to retrieve a value from the Inserted table

I'm writing a trigger for my DotNetNuke portal that requires me to read the value of a just inserted record. I believe I'm doing this right, still I'm unable to retrieve the desired value from the Inserted table. What am I doing wrong? Here is my code:

CREATE TRIGGER tr_addEditorRole ON [dbo].[UserPortals]
AFTER INSERT
AS
Declare @.Portal int
set @.Portal = (select PortalId FROM inserted)

Declare @.TabId Int
set @.Tabid = (select TabID from Tabs where Tabs.PortalID = @.Portal
and Tabs.TabName = 'MyTab')

Declare @.ModuleId int
set @.ModuleId = (SELECT ModuleId FROM Modules WHERE Modules.TabID = @.TabId
and Modules.ModuleTitle = 'MyModule')

update Modules
set
AuthorizedEditRoles = '-1;'

where ModuleId = @.ModuleIdtry changing this:
set @.Portal = (select PortalId FROM inserted)

to this:
SELECT @.Portal=PortalID FROM Inserted|||Thanks for the advice, but I've tried that syntax too - without any luck.

I'm getting no compilation errors, so the sytax is OK. I don't have any fancy tools to check what value (if any) that is being returned from the Inserted table. The problem is somewhere else...|||if PortalID is an identity column then use

SET @.Portal = @.@.Identity

@.@.Identity is the value from the LAST record inserted so as long as your only inserting one record at a time, that should work.|||I've tried that too...and get an error in my application. Also, being a newbie, I don't know if PortalID is an identity column, and given that I get an error it might not be. Can I make it an identity column? If so, could that cause my app to break somewhere else?

Furthermore, I am only inserting one record at a time, but there might be other users performing the same transaction (although with other unique values being inserted).

hmm...?