find the recent record in the list

Please start any new threads on our new site at https://forums.sqlteam.com. We've got lots of great SQL Server experts to answer whatever question you can come up with.

All Forums

SQL Server 2000 Forums

SQL Server Development (2000)

find the recent record in the list

Author

Topic

thanksfor help
Posting Yak Master

106 Posts

Posted - 2005-10-28 : 13:45:29

Hi I have the following table and have to find value for the missing item_no by taking the most recent one to that number.

item_no value
190 345
195 400
197 350
200 500

If I check for item_no 192 then it should give 345. for 189 then it should give 345. the most recent one.
Any clue for this will work
thanks in advance.

lazerath
Constraint Violating Yak Guru

343 Posts

Posted - 2005-10-28 : 14:04:36

[CODE]
SET NOCOUNT ON;

DECLARE @t TABLE (item_no int primary key, value int);
INSERT @t(item_no,value) VALUES(190,345);
INSERT @t(item_no,value) VALUES(195,400);
INSERT @t(item_no,value) VALUES(197,350);
INSERT @t(item_no,value) VALUES(200,500);

DECLARE @item_no_to_find INT;

SET @item_no_to_find = 196;

SELECT *
FROM @t
AS t1
WHERE t1.item_no
= (
SELECT MIN(t2.item_no) AS Min_Item_No
FROM @t
AS t2
WHERE ABS(t2.item_no-@item_no_to_find)
= (
SELECT MIN(ABS(t3.item_no-@item_no_to_find)) AS Closest_Item_No_Gap
FROM @t
AS t3
)
);

SET NOCOUNT OFF;[/CODE]

lazerath
Constraint Violating Yak Guru

343 Posts

Posted - 2005-10-28 : 14:08:40

Just so you know, your requirements are conflicting because you are asking for "the most recent" but what your test cases demand is the "smallest, closest item number".

I didn't spend a lot of time on this, hence the non-sargable where clause in the second tier.

madhivanan
Premature Yak Congratulator

22864 Posts

Posted - 2005-10-31 : 00:11:37

Why do you want to do this?

Madhivanan

Failing to plan is Planning to fail

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources