|
Home > Archive > Microsoft SQL Server forum > June 2005 > Two records of same Partner together
You are viewing an archived Text-only version of the thread.
To view this thread in it's original format and/or if you want to reply to
this thread please [click here]
| Author |
Two records of same Partner together
|
|
| jsfromynr 2005-06-08, 7:23 am |
| Hi all,
Here is the table and DML statments
CREATE TABLE [jatpartnerMst] (
[rowid] [int] ,
[partnerid] [int] NULL ,
[mcstat] [int] DEFAULT (1), -- 1 Pending ,2 Approved
[sf] [varchar] (20)
)
INSERT INTO [jatpartnerMst]
([rowid],[partnerid],[mcstat],& #91;sf])VALUES(1,1,2
,'active')
INSERT INTO [jatpartnerMst]
([rowid],[partnerid],[mcstat],& #91;sf])VALUES(2,1,2
,'active')
INSERT INTO [jatpartnerMst]
([rowid],[partnerid],[mcstat],& #91;sf])VALUES(3,1,2
,'active')
INSERT INTO [jatpartnerMst]
([rowid],[partnerid],[mcstat],& #91;sf])VALUES(4,1,2
,'active')
INSERT INTO [jatpartnerMst]
([rowid],[partnerid],[mcstat],& #91;sf])VALUES(5,1,1
,'active')
INSERT INTO [jatpartnerMst]
([rowid],[partnerid],[mcstat],& #91;sf])VALUES(6,1,2
,'inactive')
INSERT INTO [jatpartnerMst]
([rowid],[partnerid],[mcstat],& #91;sf])VALUES(7,1,2
,'inactive')
INSERT INTO [jatpartnerMst]
([rowid],[partnerid],[mcstat],& #91;sf])VALUES(8,1,2
,'inactive')
INSERT INTO [jatpartnerMst]
([rowid],[partnerid],[mcstat],& #91;sf])VALUES(9,2,2
,'active')
INSERT INTO [jatpartnerMst]
([rowid],[partnerid],[mcstat],& #91;sf])VALUES(10,2,
1,'active')
INSERT INTO [jatpartnerMst]
([rowid],[partnerid],[mcstat],& #91;sf])VALUES(11,1,
2,'active')
What I wish to find is the latest record on the top and it's other
records
e.g If partnerID 1 is changed it goes to the bottom of the table , at
any given time I am interested only in max(rowid) for a partner with
stat 1 or 2
I am using this query
select * from jatpartnerMst where rowid in (select max(rowid) from
jatpartnermst where mcstat in (1,2) group by partnerid,mcstat )
This query does not give me the latest.
On using this query
select * from jatpartnerMst where rowid in (select max(rowid) from
jatpartnermst where mcstat in (1,2) group by partnerid,mcstat )
order by rowid desc
The partner's two records get seperated . I wish to show them following
one another.
So the output should be
11 1 2 active
5 1 1 active
9 2 2 active
10 2 1 active
11 & 5 rowids are following each other because they are rows of same
partner and 11 is the most recent row [ because new rows are inserted
at the end]
Is it possible to do the above using single query
I am using cursor to do the same.
With Warm regards
Jatinder
| |
| Chandra 2005-06-08, 7:23 am |
| Hi
For this you might require to see
"Expanding Hierarchies" in BOL
here is the example
CREATE PROCEDURE expand (@current char(20)) as
SET NOCOUNT ON
DECLARE @level int, @line char(20)
CREATE TABLE #stack (item char(20), level int)
INSERT INTO #stack VALUES (@current, 1)
SELECT @level = 1
WHILE @level > 0
BEGIN
IF EXISTS (SELECT * FROM #stack WHERE level = @level)
BEGIN
SELECT @current = item
FROM #stack
WHERE level = @level
SELECT @line = space(@level - 1) + @current
PRINT @line
DELETE FROM #stack
WHERE level = @level
AND item = @current
INSERT #stack
SELECT child, @level + 1
FROM hierarchy
WHERE parent = @current
IF @@ROWCOUNT > 0
SELECT @level = @level + 1
END
ELSE
SELECT @level = @level - 1
END -- WHILE
Please let me know if it solves the purpose..
best Regards,
Chandra
http://groups.msn.com/SQLResource/
http://chanduas.blogspot.com/
---------------------------------------
*** Sent via Developersdex http://www.droptable.com ***
| |
| jsfromynr 2005-06-09, 3:23 am |
| Hi Chandra,
Thanks for your reply and time . But I am looking for a "Set Based
Solution"
With warm regards
Jatinder
| |
| Erland Sommarskog 2005-06-09, 3:23 am |
| jsfromynr (jatinder. singh@clovertechnolo
gies.com) writes:
> What I wish to find is the latest record on the top and it's other
> records
> e.g If partnerID 1 is changed it goes to the bottom of the table , at
> any given time I am interested only in max(rowid) for a partner with
> stat 1 or 2
Latest? I don't see any datetime column, so what is your definition
of latest?
--
Erland Sommarskog, SQL Server MVP, esquel@sommarskog.se
Books Online for SQL Server SP3 at
http://www.microsoft.com/sql/techin.../2000/books.asp
| |
| jsfromynr 2005-06-09, 3:23 am |
| Hi Erland,
Thanks for your time.
What I wish to achieve is that If a partner let us say with id 1 having
rowid 2 is changed it goes to end of the list so if there are 10
records the partner id 1 will now have rowid 11 as the latest record [
sorry If my description earlier didnot reveal it . The rowid column I
am using beahve like identity ] . There can be many more records of
partnerid 1 . but I am interested in records of a partner having mcstat
1 and 2 only and again these status should be of max rowid. That is if
there are five records of partner id 1 then only one record of that
partner with status 2 and one with status 1 should appear.
(At any given time maximum two records of a partner should appear and
the latest updated partner should appear at the top)
I am using this query
select * from jatpartnerMst where rowid in (select max(rowid) from
jatpartnermst where mcstat in (1,2) group by partnerid,mcstat )
This query does not give me the latest, because the records of partner
are seperated
On using this query
select * from jatpartnerMst where rowid in (select max(rowid) from
jatpartnermst where mcstat in (1,2) group by partnerid,mcstat )
order by rowid desc
The latest ( with maximum rowid ) record comes at the top but again
other records are disturbed
11 1 2 active
5 1 1 active
9 2 2 active
10 2 1 active
11 & 5 rowids are following each other because they are rows of same
partner and 11 is the most recent row [ because new rows are inserted
at the end]
Is it possible to do the above using single query
I am using cursor to do the same.
With Warm regards
Jatinder
| |
| Erland Sommarskog 2005-06-09, 8:23 pm |
| jsfromynr (jatinder. singh@clovertechnolo
gies.com) writes:
> On using this query
> select * from jatpartnerMst where rowid in (select max(rowid) from
> jatpartnermst where mcstat in (1,2) group by partnerid,mcstat )
> order by rowid desc
>
> The latest ( with maximum rowid ) record comes at the top but again
> other records are disturbed
>
> 11 1 2 active
> 5 1 1 active
> 9 2 2 active
> 10 2 1 active
>
>
> 11 & 5 rowids are following each other because they are rows of same
> partner and 11 is the most recent row [ because new rows are inserted
> at the end]
I might be missing something here, but isn't the case of just finding
the correct ORDER BY clause? This looks good to me:
select * from jatpartnerMst where rowid in (select max(rowid) from
jatpartnerMst where mcstat in (1,2) group by partnerid,mcstat )
order by partnerid, mcstat desc, rowid
--
Erland Sommarskog, SQL Server MVP, esquel@sommarskog.se
Books Online for SQL Server SP3 at
http://www.microsoft.com/sql/techin.../2000/books.asp
| |
| jsfromynr 2005-06-10, 3:23 am |
| Hi Erland,
Thanks for pointing it out,
Thanks again for your time and effort.
I inserted this record
INSERT INTO [jatpartnerMst]
([rowid],[partnerid],[mcstat],& #91;sf])VALUES(12,3,
2,'active')
and tried
select * from jatpartnerMst where rowid in (select max(rowid) from
jatpartnerMst where mcstat in (1,2) group by partnerid,mcstat )
order by partnerid, mcstat desc, rowid
The output was
............
..........
10 2 1 active
9 2 2 active
12 3 2 active -------------- Latest record
What I understand from the [order by] clause is that it look for next
column in [order by] list if the values of prev column of [order by]
list matches.
Now in case of [order by] clause suggested
partnerid,mcstat desc,rowid
First it(RDBMS) arrange data according to partnerid then for matching
group of partnerids arrange data according to mcstat .
Now mcstat value is either 1 or 2 . so it is not going ot look for
rowid
May be I am wrong .
With warm regards
Jatinder
| |
| jsfromynr 2005-06-10, 3:23 am |
| Hi Erland,
I tried this and it worked. Thanks for your suggestions you gave in
replacing Cube,Compute by I tried the same approach here .
It may not be the elagent method but it works .
select X1.* from jatpartnerMst X1 ,(
select 1 as oclause,partnerid,mc
stat,max(rowid) rowid from
jatpartnerMst where partnerid in (select partnerid from jatpartnerMst
where rowid=(select max(rowid) from jatpartnerMst ))
group by partnerid,mcstat
union
select 2 as oclause,partnerid,mc
stat,max(rowid) rowid from
jatpartnerMst where partnerid not in (select partnerid from
jatpartnerMst where rowid=(select max(rowid) from jatpartnerMst ))
group by partnerid,mcstat
) X2 where X1.rowid=X2.rowid order by X2.oclause
It is after joining this group I try to figure out some "Set based
solution" instead of trying cursor based solution . Can you suggest a
site or two which can guide on some methods of converting a cursor
based solution into Set Based Solution ?
With warm regards
Jatinder
| |
| Erland Sommarskog 2005-06-10, 8:23 pm |
| jsfromynr (jatinder. singh@clovertechnolo
gies.com) writes:
> It is after joining this group I try to figure out some "Set based
> solution" instead of trying cursor based solution . Can you suggest a
> site or two which can guide on some methods of converting a cursor
> based solution into Set Based Solution ?
What definitely comes to mind is to read SQL Server MVP Itzik Ben-Gans
"T-SQL Black Belt" columns in SQL Server Magazine. You find the archive
on http://www.windowsitpro.com/Authors...D/638/638.html. The more
recent articles are subscriber-only, but I believe that the older
articles freely available.
He and SQL Server MVP Tom Moreau also wrote a book on SQL 2000 programming
which you may find worthwhile.
--
Erland Sommarskog, SQL Server MVP, esquel@sommarskog.se
Books Online for SQL Server SP3 at
http://www.microsoft.com/sql/techin.../2000/books.asp
| |
| jsfromynr 2005-06-11, 3:24 am |
| Thanks Erland
The site gave me valuable information .
Jatinder
|
|
|
|
|