How to Return multiple columns, but remove duplicates of 1 column

Question

I'm trying to write a query to return all columns, and filter by displaying DISTINCT records from a specific column.

Here is my Query right now, which requires me to "Group By" everything in the select statement. I obviously don't want to do this.:

SELECT     lVisitID, sFirstName, sLastName, sAddress1, sStoreNumber
FROM        Customers
WHERE     (dtServiceDate < '5/1/15') AND (sStoreNumber = '123') 
GROUP BY sAddress1

I've also tried the below, but that returns duplicates:

    SELECT     lVisitID, MAX(sAddress1)
    FROM        Customers
    WHERE     (dtServiceDate < '5/1/15') AND (sStoreNumber = '123') 
    GROUP BY sAddress1, lvisitID

Here is my data:

lVisitID     sFirstName   sLastName     sAddress1     sStoreNumber
  1             Bob          Jones         14 Place     123
  2             Jim          Bibby         12 Place     123
  3             John         Smith         12 Place     123
  4             Jen         Jones          22 Place     193
  6             Kim         Smith          15 Place     123

The idea here would be to return Only distinct addresses, and the store number. When I attempt the above, I need:

  1             Bob          Jones         14 Place     123
  2             Jim          Bibby         12 Place     123
  6             Kim         Smith          15 Place     123

Please tag your question with the database you are using.

Gordon Linoff
– Gordon Linoff

2015-07-09 02:30:05 +00:00
Commented Jul 9, 2015 at 2:30 — Gordon Linoff
– Gordon Linoff, Commented Jul 9, 2015 at 2:30

Gordon Linoff · Accepted Answer · 2015-07-09 02:31:16Z

3

If you want just the address that has the most recent visit, use row_number():

select c.*
from (select c.*,
             row_number() over (partition by address order by lVisitId desc) as seqnum
      from customers c
     ) c
where seqnum = 1;

answered Jul 9, 2015 at 2:31

Gordon Linoff

1.3m62 gold badges706 silver badges857 bronze badges

Sign up to request clarification or add additional context in comments.

Comments

Vijay Mistry · Accepted Answer · 2015-07-09 04:45:42Z

1

I tried this on Postgres SQL.

SELECT     Distinct on (sAddress1) *
FROM        Customers
WHERE     (dtServiceDate < '5/1/15') AND (sStoreNumber = '123')

answered Jul 9, 2015 at 4:45

Vijay Mistry

1575 bronze badges

Collectives™ on Stack Overflow

How to Return multiple columns, but remove duplicates of 1 column

2 Answers 2

Comments

Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

2 Answers 2

Comments

Comments

Your Answer

Sign up or log in

Post as a guest

Related