November 11, 2009 at 4:50 pm
I have a table with an INT identity, Company and Type, among other columns. I need to match a row with the previous instance of that row for that Company and Type. The performance for the where clause is killing the query (its a large table):
select T.UniqueID,
T.Company,
T.Type,
T.DollarAmount,
P.PrevUniqueID,
P.PrevDollarAmount
from TableName T
where PrevUniqueID = select max(UniqueID) as PrevUniqueID
from TableName as P
where T.Company = P.Company
and T.Type = P.Type
and T.UniqueID > P.UniqueID
The above is psuedo-code, but hopefully it gives you an idea about what I'm trying to do.
Any help is appreciated.
November 11, 2009 at 6:17 pm
andre_quitta (11/11/2009)
I have a table with an INT identity, Company and Type, among other columns. I need to match a row with the previous instance of that row for that Company and Type. The performance for the where clause is killing the query (its a large table):select T.UniqueID,
T.Company,
T.Type,
T.DollarAmount,
P.PrevUniqueID,
P.PrevDollarAmount
from TableName T
where PrevUniqueID = select max(UniqueID) as PrevUniqueID
from TableName as P
where T.Company = P.Company
and T.Type = P.Type
and T.UniqueID > P.UniqueID
The above is psuedo-code, but hopefully it gives you an idea about what I'm trying to do.
Any help is appreciated.
Would you please post the actual code? Can't really help you with what you have posted.
Also, the DDL (CREATE TABLE statement), sample data, and expected results would also greatly help.
Please read the first article I reference in my signature block below regarding asking for help. Following the instructions in that article will get you much better help, plus the benefit of tested code.
November 12, 2009 at 6:36 am
Since you posted in a 2000 forum I'm going to suggest that you use 2 steps to solve the problem.
Step 1 - using a table variable or temp table load all the values for the desired company with an identity column to get a row number in the desired order.
Step 2 - query the loaded data using a left join to itself where A.row_number = B.row_number -1 (This gets the previous row.
Jack Corbett
Consultant - Straight Path Solutions
Check out these links on how to get faster and more accurate answers:
Forum Etiquette: How to post data/code on a forum to get the best help
Need an Answer? Actually, No ... You Need a Question
Viewing 3 posts - 1 through 2 (of 2 total)
You must be logged in to reply to this topic. Login to reply