CODEWITHSUNDEEP
sql
Davy Lambrechts
Davy Lambrechts

Reputation: 13

SQL select rows with unique values

I'm having a problem getting the rows I need from my database.

The question is simple: I have a table with customer data in it. Now I want to do a mailing (via post) to all my customers, but I only want to send it to every address once. So if any customers live on the same address (street, number, postal code, city) I only want to send the letter to the oldest person living at that address.

The fields I need are title, last name, first name, street, number, ... (for exporting purposes). I tried using group by on the address fields but then I get the error I need to use an aggregate function on the other fields (name, ...) and I don't want to do that...

Any suggestions?

Upvotes: 1

Views: 231

Answers (4)

Emma
Emma

Reputation: 277

Here's how I would do something like this in Oracle:

--Create testing table
CREATE TABLE UniqueValTest (
  fname NVARCHAR2(100),
  lname NVARCHAR2(100),
  address NVARCHAR2(100),
  city NVARCHAR2(50),
  state NVARCHAR2(2),
  zip NVARCHAR2(5),
  age NUMBER,
  recid NUMBER
  );

--Create sample data
INSERT INTO UniqueValTest (fname, lname, address, city, state, zip, age, recid)
VALUES ('JOHN', 'SMITH', '123 MAIN ST', 'JAMESTOWN', 'LA', '12345', 28, 1);
INSERT INTO UniqueValTest (fname, lname, address, city, state, zip, age, recid)
VALUES ('JENNIFER', 'SMITH', '123 MAIN ST', 'JAMESTOWN', 'LA', '12345', 30, 2);
INSERT INTO UniqueValTest (fname, lname, address, city, state, zip, age, recid)
VALUES ('RACHEL', 'ALLEN', '225 MAIN ST', 'JAMESTOWN', 'LA', '12345', 25, 3);
INSERT INTO UniqueValTest (fname, lname, address, city, state, zip, age, recid)
VALUES ('JOSEPH', 'ALLEN', '225 MAIN ST', 'JAMESTOWN', 'LA', '12345', 25, 4);
INSERT INTO UniqueValTest (fname, lname, address, city, state, zip, age, recid)
VALUES ('MARK', 'MCBRIDE', '228 MAIN ST', 'JAMESTOWN', 'LA', '12345', 55, 5);

--Here's the real part, pulling the data with the dedupe and priority
CREATE TABLE TestDataPull AS
  SELECT T.*, ROW_NUMBER() OVER (PARTITION BY lname, address, zip ORDER BY lname, address, zip, age DESC NULLS LAST) AS dupeid
  FROM UNIQUEVALTEST T;

--Now you can easily select your data
SELECT fname, lname, address, city, state, zip FROM TestDataPull WHERE dupeid = 1;

Upvotes: 0

gvee
gvee

Reputation: 17161

SELECT title
     , last_name
     , first_name
     , address_line_1
     , address_line_2
     , etc
FROM   (
        SELECT title
             , last_name
             , first_name
             , address_line_1
             , address_line_2
             , etc
             , Row_Number() OVER (PARTITION BY address_line_1, address_line_2, etc ORDER BY date_of_birth ASC) As row_number
        FROM   your_table
       ) As all_duplicatified
WHERE  row_num = 1

This gives every row a row number. The row number is "reset" on every partition (which in this case is our address fields) and the ordering of the numbers is determined by age (dob).

Therefore if we only show the ones where row_num = 1 we get just that eldest persons entry first.

Upvotes: 2

Wietze314
Wietze314

Reputation: 6020

Use SELECT DISTINCT to only select unique adresses

Upvotes: 0

cmd
cmd

Reputation: 11841

You can you the following SQL construct:

select distinct ...

Select distinct will avoid duplicates in your result set. You can use select distinct to gather all unique addresses and then cross reference with the email address

Upvotes: 0

Related Questions