How to return rows with minimum and maximum column value

Question

I have a table in a data warehouse that looks like this:

TicketNbr  Cpn  Start   End
279211      1   CHS     JFK  
295946      1   JFK     TPA  
279211      2   JFK     TPA  
234916      1   JFK     CHS
284916      1   JFK     CHS
279211      3   TPA     JFK
279211      4   JFK     CHS

I want to return rows with TicketNbr = 279211 and I only want to return the rows that have the minimum and maximum Cpn value. So from this table I want to return

TicketNbr  Cpn  Start   End       
279211      1   CHS     JFK
279211      4   JFK     CHS

I am not very proficient in SQL so I tried

SELECT
TicketNbr,
MIN(Cpn),
Start AS [StartCity],
End AS [EndCity]
FROM TKTExchange AS T
GROUP BY TicketNbr
WHERE TicketNbr = '279211'
LEFT JOIN
    (SELECT  
    MAX(Cpn),
    Start AS [StartCity],
        End AS [EndCity]
    FROM TKTExchange
    GROUP BY TicketNbr) AS GT
ON T.TicketNbr = GT.TicketNbr

But GROUPBY doesn't work in this context. How could I go about doing this?

Answer 1

Another option is using WITH TIES in concert with row_number()

Example

Select Top 1 With Ties * 
 From  YourTable
 Where TicketNbr = 279211 
 Order By Row_Number() over (Partition By [TicketNbr] Order by Cpn Desc)
         *Row_Number() over (Partition By [TicketNbr] Order by Cpn Asc) 

Returns

TicketNbr   Cpn Start   End
279211      1   CHS     JFK
279211      4   JFK     CHS

dbFiddle