Duplicate Rows 1

[rez3366]

I have a table with the following structure :

NGP NPA ZIP Dfault

"AAR" "734" "48104" ""
"ABA" "256" "35951" ""
"ABC" "626" "91770" ""
"ABL" "325" "79607" ""
"ABL" "915" "79607" ""
"ABN" "253" "98003" ""
"ABY" "541" "97321" ""
"ACA" "256" "35010" ""
"ACC" "805" "93422" ""
"ACT" "978" "02119" ""
"ADR" "517" "49221" ""
"AFW" "682" "76012" ""
"AFW" "817" "76014 " "Y"
"AGA" "229" "31707" ""
"AGC" "805" "93420" ""
"AHC" "224" "60005 " "Y"
"AHI" "847" "60005" "Y"
"AHI", "888" "60005" ""

I need to extract unique values based on the first column
NGP. So for example there are 3 instances of "AHI" and I need to have only one of those pulled from the table. In addition only duplicate values with the default column marked as "Y" should be retrieved so the row "AHI" "888 "60005" would be ignored as would one of the other "AHI" rows even though it has it's dfault column is marked as "Y" since a duplicate would not be pulled out.
Many thanks in advance.

 

[rac2]

There are a couple of ways to unduplicate rows, one way uses the DISTINCT keyword, the other way uses GROUP BY. Either way the idea of duplicate applies to the columns in the SELECT list, only the columns in the list are used to match rows.

In your sample data it looks like NGP and ZIP are the basis of duplication. So

SELECT DISTINCT NGP, ZIP
FROM Dialups
WHERE Dfault = 'Y'

or

SELECT MIN(NGP) AS "NGP", MIN(ZIP) AS "Zip"
FROM Dialups
WHERE Dfault = 'Y'
GROUP BY NGP, ZIP

which seems a bit wacky, but it works.

The reason it is interesting because it leadS to retrieving only those combinations which actually have duplicates, as follows

SELECT MIN(NGP) AS "NGP", MIN(ZIP) AS "Zip"
FROM Dialups
WHERE Dfault = 'Y'
GROUP BY NGP, ZIP
HAVING COUNT(*) > 1