Question

How to take value based on position from a list in sql server?

Answer 1

On databases that support it, you could use ROW_NUMBER() for this purpose:

SELECT RowNr FROM ( SELECT ROW_NUMBER() OVER (ORDER BY mycolumn) AS RowNr, mycolumn FROM mytable ) sub WHERE sub.mycolumn = 42

The example assumes you're looking for primary key 42 :)

The subquery is necessary because something like:

SELECT ROW_NUMBER() OVER (ORDER BY mycolumn) AS RowNr FROM mytable WHERE sub.mycolumn = 42

Will always return 1; ROW_NUMBER() works after the WHERE, so to speak.