Getting Matching Records

Hi,

I have the following table:

***********************************************************************************************
Sheet Cycle

Init SC 89
Post NCOA 89
Post Supp 89
Revised Final State Counts 89
Revised Final State Counts 94
***********************************************************************************************

Since "Revised Final State Counts" appears in both cycles 89 & 94. How can I query the table so that I only get that 1 record?

Thanks

You can do query like below:

select t.sheet, min(t.Cycle) as Cycle -- max will also do or avg depending on what you want

from table t

group by t.sheet

If you need to retrieve other columns also then you can do below in SQL Server 2000 (assuming that the combination of (sheet,cycle) is unique):

select t1.sheet, t1.cycle, t1....

from table as t1

where t1.cycle in (

select top 1 t2.cycle

from table as t2

where t2.sheet = t1.sheet

order by t2.cycle desc -- or asc

)

You can do above in SQL Server 2005 like:

select t2...

from (

-- you could use rank/dense_rank also depending on your requirements

select row_number() over(partition by t1.sheet order by t1.cycle desc) as rownum

, t1.sheet, t1.cycle, t1....

from table as t1

) as t2

where t2.rownum = 1