alter PROCEDURE TSShwRelPrts
@ParticipantId bigint
AS
BEGIN
select distinct (a.username),a.pname,a.RecommanderId,a.ParticipantId,a.relatedpid,b.recom1,i.allowselect,r.RelationshipId,r.Relname from
(select p.username,p.pname,g.ParticipantId,g.relatedpid,g.RecommanderId,g.Relationshipid
from TSGroupParticipants as g inner join tsparticipants as p
on g.relatedpid=p.ParticipantId and g.ParticipantId=@ParticipantId) as a
inner join
(select r.username,isnull(r.pname,'admin') as recom1,gr.ParticipantId,gr.relatedpid,gr.RecommanderId,gr.Relationshipid from TSGroupParticipants as gr left outer join tsparticipants as r
on gr.RecommanderId=r.ParticipantId where gr.ParticipantId=@ParticipantId) as b
on a.RecommanderId=b.RecommanderId
inner join TSMailinvitation as i on a.ParticipantId=i.ParticipantId
inner join TSRelationships as r on r.Relationshipid=a.Relationshipid
END
.....................
in the above i want to include
order by rowid desc
|
| Author: ABitSmart 04 Nov 2009 | Member Level: Diamond | Rating:  Points: 2 |
Which table does rowid belong to?
|
| Author: anbu 04 Nov 2009 | Member Level: Gold | Rating: Points: 2 |
rowid belongs to TSGroupParticipants
|
| Author: ABitSmart 05 Nov 2009 | Member Level: Diamond | Rating: Points: 2 |
Try this,
alter PROCEDURE TSShwRelPrts
@ParticipantId bigint
AS
BEGIN
select distinct (a.username),a.pname,a.RecommanderId,a.ParticipantId,a.relatedpid,b.recom1,i.allowselect,r.RelationshipId,r.Relname from
(select p.username,p.pname,g.ParticipantId,g.relatedpid,g.RecommanderId,g.Relationshipid
from TSGroupParticipants as g inner join tsparticipants as p
on g.relatedpid=p.ParticipantId and g.ParticipantId=@ParticipantId) as a
inner join
(select r.username,isnull(r.pname,'admin') as recom1,gr.ParticipantId,gr.relatedpid,gr.RecommanderId,gr.Relationshipid from TSGroupParticipants as gr left outer join tsparticipants as r
on gr.RecommanderId=r.ParticipantId where gr.ParticipantId=@ParticipantId) as b
on a.RecommanderId=b.RecommanderId
inner join TSMailinvitation as i on a.ParticipantId=i.ParticipantId
inner join TSRelationships as r on r.Relationshipid=a.Relationshipid
Order by g.rowid
END
|