Getting the last inserted row by smalldatetime

[Normann]

I am creating a Stored Proc and I need to be able to select the last added row, now this should be made easier by the fact that I have a smalldatetime column in the table that is added every time a new row is inserted. I use MSSQL 2005 and the construction of the table is this:

CREATE TABLE temp_sensor_table
	(	
	temp_id Int Identity PRIMARY KEY,
	device_id Int NOT NULL
		REFERENCES Devices(device_id),
	temp_value NVARChar(10) NOT NULL,
	temp_date smalldatetime NOT NULL DEFAULT GETDATE()
	)

And the select statement that i have is this:

select temp_value 
from temp_sensor_table 
where device_id = @device_id_temp 
and temp_date = ???

The @device_id_temp is just an int that is selected in another select statement in the Stored Proc, what I need is the last part of the where statement.

NormannTheDane

[almaz]

;with Ordered(temp_value, Position) as
(
	select temp_value, Position = row_number() over(order by temp_date desc)
	from temp_sensor_table 
	where device_id = @device_id_temp 
)
select temp_value 
from Ordered 
where Position = 1

[Normann]

The select statement from above works perfectly, if it is on it's own but when I try to use it in the Stored Proc I get an error.

I think it is when I try to use it to set a parameter, but I might be wrong. I have include what I am trying to do, and I hope someone can spot what I am doing wrong.

CREATE PROCEDURE dbo.SP_Get_Data_Status
(
@room_name nvarchar(20),			--Input vari from C#
@temp_value nvarchar(4) output,			--Output vari to C#	
@humid_value nvarchar(4) output			--Output vari to C#
)
AS
	DECLARE @room_id int			
	DECLARE @device_type_id_temp int		
	DECLARE @device_type_id_humid int	--Vari used in 
	DECLARE @device_type_id_fire int	   --the Stored Proc
	DECLARE @device_id_temp int		
	DECLARE @device_id_humid int		

	
SET @room_id = (				    --Gets the room ID 
select room_id from Rooms			--from Rooms Table
where room_name = @room_name)		--using @room_name

SET @device_type_id_temp = (			--Gets the type ID 
select device_type_id from Device_Type	      --from Device_Type Table
where device_type_name = 'temp')		--using @room_id

SET @device_type_id_humid = (			--Gets the type ID
select device_type_id from Device_Type	       --from Device_Type Table
where device_type_name = 'humid')		--using @room_id

SET @device_id_temp = (			           --Gets the device ID
select device_id from Devices			    --from Devices Table
where room_id = @room_id and			--using @room_id &
device_type_id = @device_type_id_temp)	    --@device_type_id_temp

SET @device_id_humid = (			  --Gets the device ID
select device_id from Devices			    --from Devices Table
where room_id = @room_id and			--using @room_id &
device_type_id = @device_type_id_humid)	    --@device_type_id_humid


--Code from TheScripts.com forum By almaz Thread_id: 707431
;with Ordered(temp_value, Position) as		
(
select temp_value, Position = row_number() 
over(order by temp_date desc)
from temp_sensor_table
where device_id = '1'
)
SET @temp_value = (	--<-- Error happens here
select temp_value
from Ordered
where Position = 1)

When I try to execute it the error I recive is:

Msg 156, Level 15, State 1, Procedure SP_Get_Data_Sta tus, Line 45
Incorrect syntax near the keyword 'SET'.

[almaz]

;with Ordered(temp_va lue, Position) as
(
select temp_value, Position = row_number()
over(order by temp_date desc)
from temp_sensor_tab le
where device_id = '1'
)
select @temp_value = temp_value
from Ordered
where Position = 1

[Normann]

Thank you very much for your help, it is working now

NormannTheDane