I need a query to return rows that will be used in an insert statement.
I think the easiest way to explain is with a diagram:

Table1 contains the following rows:
SubjName FromName ArchviedDate
-------- -------- -------------
Subj1    From1    1/1/2005
Subj2    From1    NULL
Subj1    From2    3/3/1999

I need a query to return this:
NewSubj  From1    NULL
NewSubj  From2    3/3/1999

Explanation: I need to pair a new SubjName with every existing FromName
(but without creating duplicates). The complication is with the
ArchivedDate, I need it to be so that even if there are multiple
ArchivedDates associated with a single FromName, only one row is
returned, with NULL taking precedence over any date value. The query I
have so far is:

SELECT DISTINCT 'NewSubj', FromSender, ArchivedDate, FromID, FROM
Table1

The problem is that this query returns the following:
NewSubj  From1    NULL
NewSubj  From1    1/1/2005
NewSubj  From2    3/3/1999

The second row in that result set shouldn't be there because I only
want a single row for each existing FromName, with NULL ArchivedDates
taking precedence. Unfortunately I have no idea how to do this. Any
ideas? Please!

Tom,

The complication is with the
ArchivedDate, I need it to be so that even if there are multiple
ArchivedDates associated with a single FromName, only one row is
returned, with NULL taking precedence over any date value

What happens if you have two valid ArchivedDates?  Is that even a
possibility?

Stu

Multiple valid ArchivedDates is possible, but it doesn't matter which
one is selected. The only requirement is that if there is a NULL
ArchivedDate that that one is selected over any non-null ones.

SELECT 'NewSubj', fromname,
CASE WHEN COUNT(*)=COUNT(archiveddate) THEN MAX(archiveddate) END
FROM Table1
GROUP BY fromname ;

David, your query worked perfectly! Thanks.

composite key structure
Default Column Values
Finding similar sales
return set of only first unique row
Passing a variable to multiple rows in an INSERT