February 20, 2003 at 3:19 pm
i have a variable to which i want to assign the value from a specific field in the 6th row of a recordset. nothing is working. help?
February 20, 2003 at 3:37 pm
Can you explain in more detail your code. Are we talking SQL variable or ASP variable and how many items. An example would help.
February 20, 2003 at 3:43 pm
my code begins like this:
"
select top 6 * from tblMachineInfo
order by ID_Machine_Info desc
declare @@ID_Machine_Info char(4)
"
the result will be the records with the 6 highest values for the field ID_Machine_Info, a 4-digit numberic value. I want to delete the records with the 5 highest values, by assigning the 6th value to the variable and then taking everything greater than that for the delete statement.
thanks
February 20, 2003 at 4:37 pm
Would this answer your request?
DELETE tblMachineInfo
WHERE ID_Machine_Info IN
( SELECT TOP 5 ID_Machine_Info FROM tblMachineInfo ORDER BY ID_Machine_Info DESC )
February 20, 2003 at 5:00 pm
yes, almost completely, thanks; but i still would like to know how to assign the previously-described value to the variable, for various other reasons. can that be done?
also, in the code you posted, i tried to add a "where Date = GetDate() " clause right before the "ORDER BY" statement; to ensure that only the current day's data can be deleted in this manner. The result returned 0 rows, even though there are 5 rows that meet the criteria. Any advice?
February 21, 2003 at 6:50 am
To get the MachineIDs into a variable, do a SELECT INTO a #temp table before doing the update.
As for the GETDATE() part of your question, ensure that you strip the time information from the GETDATE() return so as to only compare the date part of the value:
WHERE Convert(CHAR(10), DateField) = Convert(CHAR(10), GETDATE())
February 24, 2003 at 1:06 am
Also consider using the DateDiff function.
E.g. WHERE DATEDIFF(d, date, GetDate()) = 0
February 24, 2003 at 11:21 am
How about this (assuming your ID field is an identity column) --
DECLARE @ID_Machine_Info int
SELECT TOP 1 @ID_Machine_Info = MI.ID_Machine_Info FROM
(SELECT TOP 6 ID_Machine_Info FROM tblMachineInfo
WHERE Date >= CONVERT(CHAR(10), GETDATE(), 102)
ORDER BY ID_Machine_Info DESC) MI
ORDER BY MI.ID_Machine_Info
IF NOT @ID_Machine_Info IS NULL
DELETE tblMachineInfo
WHERE ID_Machine_Info > @ID_Machine_Info
Viewing 8 posts - 1 through 7 (of 7 total)
You must be logged in to reply to this topic. Login to reply