CODEWITHSUNDEEP
sql-server-2008
Rabin
Rabin

Reputation: 1583

Max and Min in the same table

Suppose I have the following table:

id      flag      date
 1         1      2012-01-01
 2         1      2012-02-01
 3         1      2012-03-01
 4         0      2012-04-01
 5         0      2012-05-01
 6         0      2012-06-01

Is there anyway I can get maximum date for rows with flag as 1 and minimum date for row with flag as 0 using the same query?

Edited: I want the result to looks something like this:

max           min
2012-03-01    2012-04-01

Thank you.

Upvotes: 2

Views: 244

Answers (3)

Adriaan Stander
Adriaan Stander

Reputation: 166376

You can try something like

SELECT  MAX(CASE WHEN FLAG = 1 THEN Date ELSE NULL END) MaxFlag1,
        MIN(CASE WHEN FLAG = 0 THEN Date ELSE NULL END) MinFlag0
FROM    [Table]

Upvotes: 3

juergen d
juergen d

Reputation: 204756

try

select flag,
       case when flag = 1 then max([date])
            when flag = 0 then min([date])
       end as date
from your_table
group by flag

Upvotes: 1

Lieven Keersmaekers
Lieven Keersmaekers

Reputation: 58431

You can use a UNION (ALL) to combine the results of two statements. The only restriction on those statements is that they both return the same amount and same type of columns.

Applied to your example, this would become

SELECT MAX(date) FROM YourTable WHERE flag = 1
UNION ALL SELECT MIN(date) FROM YourTable WHERE flag = 0

Upvotes: 0

Related Questions