Posted by plaster1 on 04/09/07 20:29

On Apr 5, 4:55 pm, Erland Sommarskog <esq...@sommarskog.se> wrote:
> (plast...@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, esq...@sommarskog.se
>
> Books Online for SQL Server 2005 athttp://www.microsoft.com/technet/prodtechnol/sql/2005/downloads/books...
> Books Online for SQL Server 2000 athttp://www.microsoft.com/sql/prodinfo/previousversions/books.mspx- Hide quoted text -
>
> - Show quoted text -

Thanks for the response, and sorry for the lack of info: SQL ver 7.0

-b

[Back to original message]