How do you find a previous key based on where criteria

[JBellTek]

I am working out a single-use query for populating a new table structure from existing data.

I have:
tbl1
id, name, value

I want:
tbl2
id, name, value, prior_id

So far I have a select query that populates everything but the prior_id column. I was solving this with a subquery for that column in the column list, like so:

select top 1 ID
from tbl1
where ID < (select top 1 ID from tbl1 where name = 19 order by ID desc)
and ID not in (select prior_id from tbl2 where name = 19 order by ID2 desc)
and name = 19
order by ID desc

This works great for getting the second most recent ID for [name], however I want the prior_id for the [name] I am currently looking at. There are multiple [name]s being tracked in this table, so there will probably be multiple IDs in between records for [name].

I have considered DIMing a var for what the current ID is, but have had issues getting it to work considering SQL's right-to-left interpretation. Let me know if I need to clarify anything on my question.

 

[starsky51]

There are a few ways you can do this. But, an Apply is the most useful since it can also be used to pick up other fields, such as the name or value field, from the previous row.
Here's an example:

DECLARE @tbl1 TABLE (id int, name varchar(50), value int)

INSERT INTO @tbl1
	  SELECT 1, 'Anne', 10
UNION SELECT 3, 'Bill', 20
UNION SELECT 6, 'Carl', 30
UNION SELECT 7, 'Dave', 40
UNION SELECT 9, 'Evan', 50

SELECT *
FROM @tbl1 AS t1main
OUTER APPLY (SELECT TOP 1 id AS prior_id FROM @tbl1 WHERE id < t1main.id ORDER BY id DESC) AS t1prior