General discussion

Locked

SQL - Compare Previous Record

By MK1 ·
I need to look at an ID number and compare it to the previous ID number in the result set. If they are different I need to tag the first record as being first and then continue to compare the remaining records

The results would need to look as follows

ID Amount First
1 100 Y
1 120
1 140
2 200 Y
2 145

This conversation is currently closed to new comments.

11 total posts (Page 1 of 2)   01 | 02   Next
| Thread display: Collapse - | Expand +

All Comments

Collapse -

SQL - Compare Previous Record

by anthony In reply to SQL - Compare Previous Re ...

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

Collapse -

SQL - Compare Previous Record

by MK1 In reply to SQL - Compare Previous Re ...

Unfortunately to my knowledge there is no "loop" type structure in SQL so unfortunately I don't think I can use your solution

Collapse -

SQL - Compare Previous Record

by john_wills In reply to SQL - Compare Previous Re ...

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;

Collapse -

SQL - Compare Previous Record

by MK1 In reply to SQL - Compare Previous Re ...

It is not actually the minimum amount I am tagging it is the very first instance and this could be any possible amount

Collapse -

SQL - Compare Previous Record

by john_wills In reply to SQL - Compare Previous Re ...

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.

Collapse -

SQL - Compare Previous Record

by MK1 In reply to SQL - Compare Previous Re ...

The question was auto-closed by TechRepublic

Collapse -

SQL - Compare Previous Record

by frostbite In reply to SQL - Compare Previous Re ...

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

Collapse -

SQL - Compare Previous Record

by MK1 In reply to SQL - Compare Previous Re ...

The question was auto-closed by TechRepublic

Collapse -

SQL - Compare Previous Record

by nk196974 In reply to SQL - Compare Previous Re ...

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

Collapse -

SQL - Compare Previous Record

by MK1 In reply to SQL - Compare Previous Re ...

The question was auto-closed by TechRepublic

Back to Web Development Forum
11 total posts (Page 1 of 2)   01 | 02   Next

Related Discussions

Related Forums