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?

John Cappelletti · Accepted Answer · 2019-05-01 18:30:48Z

2

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

answered May 1, 2019 at 18:30

John Cappelletti

82.3k7 gold badges50 silver badges75 bronze badges

Sign up to request clarification or add additional context in comments.

Comments

forpas · Accepted Answer · 2019-05-01 18:29:33Z

1

With group by TicketNbr you can get the min and max values you need and then join to the table. If there are more rows with equal Cpn they will all be returned:

select t.* from TKTExchange t
inner join (
  select TicketNbr, min(Cpn) mincpn, max(Cpn) maxcpn
  from TKTExchange
  group by TicketNbr
) g on g.TicketNbr = t.TicketNbr and t.Cpn in (g.mincpn, g.maxcpn)

You can add conditions like:

where t.TicketNbr = 279211

answered May 1, 2019 at 18:29

forpas

165k10 gold badges51 silver badges85 bronze badges

Comments

Thom A · Accepted Answer · 2019-05-01 18:32:02Z

1

A slightly different method to the others using a CTE and windowed functions:

WITH VTE AS (
    SELECT *,
           MIN(Cpn) OVER (PARTITION BY TicketNbr) AS MinCpn,
           MAX(Cpn) OVER (PARTITION BY TicketNbr) AS MaxCpn
    FROM (VALUES(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')) V(TicketNbr,Cpn,[Start],[End])
    WHERE V.TicketNbr = 279211)
SELECT V.Cpn,
       V.Cpn,
       V.[Start],
       V.[End]
FROM VTE V
WHERE V.Cpn = V.MinCpn
   OR V.Cpn = V.MaxCpn;

answered May 1, 2019 at 18:32

Thom A♦

97.7k12 gold badges67 silver badges102 bronze badges

Comments

DBro · Accepted Answer · 2019-05-01 18:43:13Z

0

One option would be to select the top and bottom records, and then UNION them together into one dataset. Such as:

SELECT * FROM (SELECT TOP 1
     [TicketNbr]
    ,[Cpn]
    ,[Start]
    ,[End]
FROM [TKTExchange]
WHERE
    [TicketNbr] = '279211'
ORDER BY
    [Cpn] ASC) AS [A]

UNION

SELECT * FROM (SELECT TOP 1
     [TicketNbr]
    ,[Cpn]
    ,[Start]
    ,[End]
FROM [TKTExchange]
WHERE
    [TicketNbr] = '279211'
ORDER BY
    [Cpn] DESC) AS [B]

edited May 1, 2019 at 18:43

answered May 1, 2019 at 18:29

DBro

4321 gold badge3 silver badges9 bronze badges

Collectives™ on Stack Overflow

How to return rows with minimum and maximum column value

4 Answers 4

Comments

Comments

Comments

Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

4 Answers 4

Comments

Comments

Comments

Comments

Your Answer

Sign up or log in

Post as a guest

Related