Posted by Erland Sommarskog on 04/05/07 21:55

(plaster1@gmail.com) writes:
> Been trying to come up with a query to filter-down my sample set into
> distinct records. For instance, lets say column1 is a sample set,
> column2 is the parameter, and column3 is a name and column4 is a type
> (lets also say there is a fifth column (id) that is an id). What I
> need is one record per type per sample only if type is given, if not,
> then return that record as well.
>...
> I want output :
>
> 1 2 3
> -------
> A 1 X P
> A 2 W
> A 3 W
> A 4 T P
> A 5 U P
> A 6 V P
> A 7 T
> A 7 U
> A 7 V

Since you did not provide CREATE TABLE and INSERT statements with the
sample data, this is untested:

SELECT col1, col2, col3, col4
FROM (SELECT col1, col2, col3, col4,
rn = row_number() OVER(PARTITION BY col1, col2
ORDER BY col3)
FROM tbl) AS d
WHERE rn = CASE WHEN col4 IS NOT NULL THEN 1 ELSE rn END


This solution requires SQL 2005. Tip: always say which version of
SQL Server you are using.


--
Erland Sommarskog, SQL Server MVP, esquel@sommarskog.se

Books Online for SQL Server 2005 at
http://www.microsoft.com/technet/prodtechnol/sql/2005/downloads/books.mspx
Books Online for SQL Server 2000 at
http://www.microsoft.com/sql/prodinfo/previousversions/books.mspx

• Next in forum: Re: Compact and Repair in an adp
• Prev in forum: Re: Update Status Field after Expiry Date
• Thread view: Re: select distinct record only if certain column not null

[Reply to this message]