How can I pass a condition to a MIN statement in TSQL

Question

I've got a table with the following columns

email, id, date 

There's a unique key on email+id, so the table can contain multiples of an email address. id can be null.

I want to return results in the format

email, first date, number of ids, first id date (if number of ids not null) 

or

[email protected], 2013-06-11, 0, NULL
[email protected], 2013-06-12, 2, 2013-06-13

My query is pretty close to what I want, while still being efficient.

SELECT TOP (@rows * @page) email, MIN([date]) as [date],COUNT(id) as [ids],
[x] = CASE COUNT(id) WHEN 0 THEN NULL ELSE MIN([date]) END
FROM Table 
GROUP BY email ORDER by MIN([date])

However, this version of the query returns the overall MIN(date) for each email, if they have any entries with a non null id

Is there some way I can nullify the MIN(date) if there's no id? I'm working on a solution that adds a large number of days to date if id is 0 and getting the MIN of this, but it seems like there must be a smarter way?

Answer 1

You want a conditional min(), so use case inside the min():

SELECT TOP (@rows * @page) email,
       MIN([date]) as [date],
       COUNT(id) as [ids],
       min(case when id is not null then [date] end) as [x]
FROM Table 
GROUP BY email
ORDER by MIN([date])