SQL server 2005 showing unique values and not showing anything duplicating

Question

On 2005 SQL Server Express I am trying to get a sql statement working where it showing unique values where the value serial number was only entered ONCE in the table. If the user would enter a serial number twice I want it to just ignore both records But I am not sure how to accomplish it. I tried this with distinct but it would still show a record.

SELECT     DISTINCT SerialNumber, ModelNumber, Date, CustomerName
FROM         PreQcFormTable
WHERE     (Date BETWEEN '2/24/2014' AND '3/5/2014') AND (ProductOrigin = 'P')  AND (FailCodes = '')

It doesn't take into account that the SerialNumber might have been inputed twice or that the SerialNumber is in more then one record. Is there something else I need to add to this to make it ignore None unique SerialNumber?

Edit 1 when I use this sql statement i would get this record

  "5554432218   7200A-70N       2014-03-04 00:00:00.000 CPE" 
  "555555   9200A-7E1100    2014-03-05 00:00:00.000 ACP"
  "89701234555  9200B-7E1100    2014-02-25 00:00:00.000 Gleen"

Issue is the 55555 Serial number has been entered already within the week when I enter this similar sql statement.

SELECT    *
FROM       PreQcFormTable
WHERE     (Date BETWEEN '2/24/2014' AND '3/5/2014') AND (ProductOrigin = 'P') AND (FailCodes != '')

results:

555555  9200A-7E1100    2014-03-05 00:00:00.000 ACP 2M is: 89 
1323465 7200A-70N            2014-02-25 00:00:00.000             ACP    1M

Knowing that I want the record removed from the first one where it would lead me to this result:

      "5554432218   7200A-70N       2014-03-04 00:00:00.000 CPE" 
  "89701234555  9200B-7E1100    2014-02-25 00:00:00.000 Gleen"

Also can you post some sample data and expected output, so we know what you are expecting from the query. — user2989408, Mar 05 '14 at 17:54
possible duplicate of [How can I remove duplicate rows?](http://stackoverflow.com/questions/18932/how-can-i-remove-duplicate-rows) — Zane, Mar 05 '14 at 18:02
@user2989408 I updated my question to hopefully explain what i am getting and wanting. — Gilbert V, Mar 05 '14 at 18:23

Gordon Linoff · Accepted Answer · 2014-03-05T19:48:28.653

2

I would use count(*) as a window function to get the number of records for a given serial number. Then, just select the rows where cnt = 1:

SELECT SerialNumber, ModelNumber, Date, CustomerName
FROM (SELECT pft.*,
             count(*) over (partition by SerialNumber) as cnt
      FROM PreQcFormTable pft
      WHERE Date BETWEEN '2014-02-14' AND '2014-03-05' AND
            ProductOrigin = 'P'
     ) t
WHERE cnt = 1 AND
      FailCodes = '';

edited Mar 05 '14 at 19:48

answered Mar 05 '14 at 17:54

Gordon Linoff

1,242,037
58
646
786

This still gives me the same result.If it helps the serialNumber is not the primarykey for this table it is PreQcForm_ID. – Gilbert V Mar 05 '14 at 18:26
@GilbertV . . . I missed the condition on `FailCodes` being different when you define duplicates. – Gordon Linoff Mar 05 '14 at 18:32
When I try this I am getting the error Msg 207, Level 16, State 1, Line 7 Invalid column name 'FailCodes'. Now I know that is the correct name of the column is there something else missing? – Gilbert V Mar 05 '14 at 18:38
I am not sure why but now I am getting this error. Msg 102, Level 15, State 1, Line 3 Incorrect syntax near 'count'. – Gilbert V Mar 05 '14 at 18:47
Had to modify the answer you gave me as this. SELECT SerialNumber, ModelNumber, Date, CustomerName FROM (SELECT SerialNumber, ModelNumber, Date, CustomerName, FailCodes, COUNT(*) OVER (PARTITION BY SerialNumber) as cnt FROM PreQcFormTable WHERE (Date BETWEEN '2/24/2014' AND '3/5/2014') AND (ProductOrigin = 'P') ) t WHERE cnt = 1 AND FailCodes = "''" but it works out fine now thank you – Gilbert V Mar 05 '14 at 19:26

SQL server 2005 showing unique values and not showing anything duplicating

1 Answers1