need help with a self join query 1

[cwbrumett]

Basically I have a flat table that contains contact details AND order details (I didn't make it .. just inherited it)

The pkey is a auto-incrementing field, what I need to do is get what the "previous" id is for a given contact, null if there was no preivous.

sample data:
RecID ContactID
6000 100
6500 100
7000 100

What I need the output to look like is:
PreviousRecID RecID ContactID
NULL 6000 100
6000 6500 100
6500 7000 100

This should be a pretty easy self join ... but I'll go ahead and blame my allergy meds on this one

Thanks in advance

 

[PWise]

Select Recid, ContactID
from table
left join (select max(recid)mrec
from table group by ContactID )sj
on sj.ContactID =table.ContactID
and mrec<Recid

 

[cwbrumett]

Thanks for the quick response

That is returning nulls for all the occurrences of mrec. here is the actual code I plugged your query into (recID = FeedRecID and ContactID = PartnerID)

Select sj.mrec, T1.FeedRecID, T1.PartnerID
from SAP_Feed T1
	left join (select max(FeedRecID) as mrec, PartnerID
			from SAP_Feed 
			group by PartnerID 
		)sj
on sj.PartnerID = T1.PartnerID
and sj.mrec < T1.FeedRecID 

WHERE T1.PartnerID = 216360

The results I got back were
mrec FeedRecID PartnerID
NULL 6926 216360
NULL 6927 216360
NULL 6928 216360


It needs to be
mrec FeedRecID PartnerID
NULL 6926 216360
6926 6927 216360
6927 6928 216360

 

[cwbrumett]

I didn't want to resort to using a while .. but never could get it working without one ... this is how I did it if anyone cares.

DECLARE @rCnt as int
SET @rCnt = (
		SELECT COUNT(A.rCnt) FROM
		(
		SELECT COUNT( F.PartnerID ) as rCnt
			FROM SAP_Feed F 
			LEFT JOIN SAP_UpdatesSetFlag U ON F.PartnerID = U.PartnerID
					AND F.FeedRecID > U.FeedRecID
		
		GROUP BY U.FeedRecID, F.PartnerID
		HAVING MAX(U.FeedRecID) NOT IN (SELECT ISNULL(PreviousFeedRecID,0) FROM SAP_UpdatesSetFlag)
		) A
	)

WHILE @rCnt > 0
BEGIN

--do actual insert ... keep going until done
INSERT INTO SAP_UpdatesSetFlag(PreviousFeedRecID, FeedRecID, PartnerID)
SELECT U.FeedRecID, MIN(F.FeedRecID) AS MINFeedRecID, F.PartnerID
FROM SAP_Feed F 
	LEFT JOIN SAP_UpdatesSetFlag U ON F.PartnerID = U.PartnerID
			AND F.FeedRecID > U.FeedRecID

GROUP BY U.FeedRecID, F.PartnerID
HAVING MAX(U.FeedRecID) NOT IN (SELECT ISNULL(PreviousFeedRecID,0) FROM SAP_UpdatesSetFlag)


--set @rCnt to # of records to process
SET @rCnt = (
		SELECT COUNT(A.rCnt) FROM
		(
		SELECT COUNT( F.PartnerID ) as rCnt
			FROM SAP_Feed F 
			LEFT JOIN SAP_UpdatesSetFlag U ON F.PartnerID = U.PartnerID
					AND F.FeedRecID > U.FeedRecID
		
		GROUP BY U.FeedRecID, F.PartnerID
		HAVING MAX(U.FeedRecID) NOT IN (SELECT ISNULL(PreviousFeedRecID,0) FROM SAP_UpdatesSetFlag)
		) A
	)

END --END WHILE

 

[cwbrumett]

I'd still really like to see if not using a while is possible ... I'm sure it is.

 

[RyanEK]

Hi,

Hmmm this should work.

select (select max(recid) from #tmp where recid < t1.recid) as PreviousRecId,
recid, contactid
from [Table] t1

 

[RyanEK]

Oops sorry... should be

select (select max(recid) from [Table] where recid < t1.recid) as PreviousRecId,
recid, contactid
from [Table] t1

 

[cwbrumett]

that worked ... I knew there was a way ... I had to add the contactID join to the nested query to work correctly.

thanks and here's a star

select (select max(FeedRecID) from SAP_Feed where FeedRecID < t1.FeedRecID and PartnerId = t1.PartnerID) as PreviousRecId,
FeedRecID, PartnerID
from SAP_Feed t1