Posted by Erland Sommarskog on 06/10/05 01:04

jsfromynr (jatinder.singh@clovertechnologies.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/techinfo/productdoc/2000/books.asp

• Next in forum: Re: How to Use Varchar with Int column
• Prev in forum: Re: SQLTransaction locking up table
• Thread view: Re: Two records of same Partner together

[Reply to this message]