SQL - Compare Previous Record

This solution is in VB.NET but you can take the idea and port it to any language.

' /// Declare your variables
Dim intLastValue as Integer = -1 ' Setting it to a negative number will cause the first record to be different

' /// Loop through a data reader
' /// This would be like doing a record movenext
' /// until the EOF
Do While drSQL.Read
If drSQL("ID") <> intLastValue Then
' /// This is different than the last number
' /// Do whatever processing you need to
End If

' Set the new intLastValue Variable
intLastValue = drSQL("ID")
Loop

This is from scratch so let me know if something doesn't make sense.

Hope this takes care of it.

Anthony Stark
President / CEO
AnthonyStark.com, Inc.
Internetand Microsoft Windows Application Development and Consulting

INSERT INTO TempTable(ViewID,MinAmount)
SELECT ID AS ViewID, Min(Amount) As MinAmount
FROM StartTable
GROUP BY ID;
UPDATE StartTable LEFT JOIN TempTable ON ID=ViewID
SET First=true
WHERE Amount=MinAmount;

By what criterion is a particular row the FIRST for its ID? What column are you ordering on after ID? Use that column with either min or max instead of Amount in Answer 2.

Hello, you could try using a cursor to do this...
try the following code below:

SET NOCOUNT ON

DECLARE @temp_id int, @temp_amt money, @temp_first bit,
@curr_id int

set @curr_id = -1

DECLARE temp_cursor CURSOR FOR
SELECT [ID], Amount, [FIRST]
FROM tblTemp

OPEN temp_cursor

FETCH NEXT FROM temp_cursor
INTO @temp_id, @temp_amt, @temp_first

WHILE @@FETCH_STATUS = 0
BEGIN

IF @curr_id <> @temp_id
BEGIN
UPDATE tblTemp SET [FIRST] = 1
WHERE CURRENT OF temp_cursor
END
SET @curr_id = @temp_id
-- Get the next row.
FETCH NEXT FROM temp_cursor
INTO @temp_id, @temp_amt, @temp_first
END

CLOSE temp_cursor
DEALLOCATE temp_cursor
GO

Hope this helps
- Jaypee

Cursor is ok if you are expecting 50-100 records
in the result set. But if you expecting more records then there will be performance penalty
for using cursor. Try this insted.
1. Create a tempory table with row id.
and copy resultset to this temporary table.
now you will have primary key for the result
set.

2. Make a self join for a table
with rowid = rowid + 1

3. execute batch update query
to update column first
where table1.ID <> table2.ID

This question was auto closed due to inactivity