Re: select distinct record only if certain column not null

(plaster1@xxxxxxxxx) 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@xxxxxxxxxxxxx

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
.

Relevant Pages

  • Re: update 2 fields problem
    ... as the SQL Server optimizer will usually generate the ... This changes if the subquery has to be duplicated. ... order in which matches are found depends on the execution plan, ... still be affected by the UPDATE statement and the new values for Column1 ...
    (microsoft.public.sqlserver.programming)
  • Re: select distinct record only if certain column not null
    ... For instance, lets say column1 is a sample set, ... Books Online for SQL Server 2005 athttp://www.microsoft.com/technet/prodtechnol/sql/2005/downloads/books ... ...
    (comp.databases.ms-sqlserver)
  • Re: Date Query
    ... to display any records from the current date and greater then. ... SELECT Column1, Column2, ... ... I recommend posting to an Access group. ... Hugo Kornelis, SQL Server MVP ...
    (microsoft.public.sqlserver.mseq)
  • Re: Rename column name using variable
    ... DECLARE @xxx VARCHAR ... column1 AS @xxx ... Beware that this will cause some recompiles, which on SQL 2005 is not ... Erland Sommarskog, SQL Server MVP, esquel@xxxxxxxxxxxxx ...
    (comp.databases.ms-sqlserver)
  • select distinct record only if certain column not null
    ... Been trying to come up with a query to filter-down my sample set into ... For instance, lets say column1 is a sample set, ... where id in from table1 ...
    (comp.databases.ms-sqlserver)