create table demo
(
    sno int primary key identity(1,1),
    cid int, 
    docno nvarchar(100),
    revisionid varchar(10) ,
    status nvarchar(100)
)

insert into demo 
values (1, 'abc', '00', 'release'),
       (2, 'abc', '01', 'release'),
       (3, 'abc', '02', 'notrelease'), 
       (4, 'xyz', '00', 'notrelease'),
       (5, 'xyz', '01', 'release'), 
       (6, 'xyz', '02', 'release'),
       (7, 'pqr', '01', 'release')

From below table I want to fetch cid for latest release status of all docno

For example: for abc ->cid=2, for xyz->cid=6,for abc ->pqr=7

CodePudding user response：

You can use below query to get your desired output:

 select docno,max(cid) from Demo where status = 'release'
 group by docno;

CodePudding user response：

Try this using sub-query:

SELECT DISTINCT demo.docno, demo.cid
FROM demo
INNER JOIN 
(
  SELECT docno, max(Revisionid) as max_rev
  FROM demo
  WHERE status = 'release'
  GROUP BY docno
) as sq
ON demo.docno=sq.docno 
AND demo.Revisionid = sq.max_rev

db<>fiddle

CodePudding user response：

In Sql Server you can use a TOP n WITH TIES in combination with ROW_NUMBER.

Handy to get the latest released cid per docno.

select top 1 with ties docno, cid
from demo
where status = 'release'
order by 
   row_number()
   over (partition by docno 
         order by cid desc, sno desc)

Demo on db<>fiddle here