select a single column value based on order in SQL server

Question

I have something like below table

create table #temp
(
 Name varchar(50)
)
INSERT INTO #temp values ('Round 1')
INSERT INTO #temp values ('Round 2')
INSERT INTO #temp values ('Round 3')

if All three are present select Round 3, if Round 1 and Round 2 are present Select Round 2 if Only Round 1 is present select Round 1 else select 'No Round'

So you want MAX(Name) ? ISNULL(MAX(Name),'No Round') if the table is empty? — Panagiotis Kanavos
Not always Max but in a given order, Round is just an example, it is as StageH, StageC ,StageAB — Bhavna
And what is that "order", @Bhavna ? How do you determine it, and, more importantly, how would SQL Server? — Larnu
if StageABC (Consider it Round 1)---> StageH (Round 2)-->StageC (Round 3) @Larnu — Bhavna