469,076 Members | 1,437 Online
Bytes | Developer Community
New Post

Home Posts Topics Members FAQ

Post your question to a community of 469,076 developers. It's quick & easy.

Getting the last inserted row by smalldatetime

17
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:
Expand|Select|Wrap|Line Numbers
  1. CREATE TABLE temp_sensor_table
  2.     (    
  3.     temp_id Int Identity PRIMARY KEY,
  4.     device_id Int NOT NULL
  5.         REFERENCES Devices(device_id),
  6.     temp_value NVARChar(10) NOT NULL,
  7.     temp_date smalldatetime NOT NULL DEFAULT GETDATE()
  8.     )
  9.  
And the select statement that i have is this:
Expand|Select|Wrap|Line Numbers
  1. select temp_value 
  2. from temp_sensor_table 
  3. where device_id = @device_id_temp 
  4. and temp_date = ???
  5.  
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
Sep 13 '07 #1
4 3625
almaz
168 Expert 100+
Expand|Select|Wrap|Line Numbers
  1. ;with Ordered(temp_value, Position) as
  2. (
  3.     select temp_value, Position = row_number() over(order by temp_date desc)
  4.     from temp_sensor_table 
  5.     where device_id = @device_id_temp 
  6. )
  7. select temp_value 
  8. from Ordered 
  9. where Position = 1
Sep 13 '07 #2
Normann
17
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.

Expand|Select|Wrap|Line Numbers
  1. CREATE PROCEDURE dbo.SP_Get_Data_Status
  2. (
  3. @room_name nvarchar(20),            --Input vari from C#
  4. @temp_value nvarchar(4) output,            --Output vari to C#    
  5. @humid_value nvarchar(4) output            --Output vari to C#
  6. )
  7. AS
  8.     DECLARE @room_id int            
  9.     DECLARE @device_type_id_temp int        
  10.     DECLARE @device_type_id_humid int    --Vari used in 
  11.     DECLARE @device_type_id_fire int       --the Stored Proc
  12.     DECLARE @device_id_temp int        
  13.     DECLARE @device_id_humid int        
  14.  
  15.  
  16. SET @room_id = (                    --Gets the room ID 
  17. select room_id from Rooms            --from Rooms Table
  18. where room_name = @room_name)        --using @room_name
  19.  
  20. SET @device_type_id_temp = (            --Gets the type ID 
  21. select device_type_id from Device_Type          --from Device_Type Table
  22. where device_type_name = 'temp')        --using @room_id
  23.  
  24. SET @device_type_id_humid = (            --Gets the type ID
  25. select device_type_id from Device_Type           --from Device_Type Table
  26. where device_type_name = 'humid')        --using @room_id
  27.  
  28. SET @device_id_temp = (                       --Gets the device ID
  29. select device_id from Devices                --from Devices Table
  30. where room_id = @room_id and            --using @room_id &
  31. device_type_id = @device_type_id_temp)        --@device_type_id_temp
  32.  
  33. SET @device_id_humid = (              --Gets the device ID
  34. select device_id from Devices                --from Devices Table
  35. where room_id = @room_id and            --using @room_id &
  36. device_type_id = @device_type_id_humid)        --@device_type_id_humid
  37.  
  38.  
  39. --Code from TheScripts.com forum By almaz Thread_id: 707431
  40. ;with Ordered(temp_value, Position) as        
  41. (
  42. select temp_value, Position = row_number() 
  43. over(order by temp_date desc)
  44. from temp_sensor_table
  45. where device_id = '1'
  46. )
  47. SET @temp_value = (    --<-- Error happens here
  48. select temp_value
  49. from Ordered
  50. where Position = 1)
  51.  

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

Msg 156, Level 15, State 1, Procedure SP_Get_Data_Status, Line 45
Incorrect syntax near the keyword 'SET'.
Sep 13 '07 #3
almaz
168 Expert 100+
;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'
)
select @temp_value = temp_value
from Ordered
where Position = 1
Sep 14 '07 #4
Normann
17
Thank you very much for your help, it is working now

NormannTheDane
Sep 14 '07 #5

Post your reply

Sign in to post your reply or Sign up for a free account.

Similar topics

5 posts views Thread by Ray via SQLMonster.com | last post: by
2 posts views Thread by mgarriss | last post: by
3 posts views Thread by Mark | last post: by
1 post views Thread by CARIGAR | last post: by
reply views Thread by zhoujie | last post: by
By using this site, you agree to our Privacy Policy and Terms of Use.