Look up ROW_NUMBER(), just note that it's not particularly fast on larger result sets.
p.s. Consider using parametrised SQL statements or stored procedures. That example line you gave is vulnerable to SQL Injection attacks.
Gail Shaw
Microsoft Certified Master: SQL Server, MVP, M.Sc (Comp Sci)
SQL In The Wild: Discussions on DB performance with occasional diversions into recoverability