[Okta Webinar] Learn how to a build a cloud-first strategyRegister Now

x
?
Solved

Sql query syntax question

Posted on 2009-12-29
11
Medium Priority
?
182 Views
Last Modified: 2012-05-08
I have this table for example
ID                     Fname            LName                 Address
1001                  Joe                Smith                  123 Test St.
1002                  John              Smith                  123 Test St
1003                  Jerry              Smith                  123 Test St.
The question is..
All 3 persons live in same addresses. How can only one be mailed?
0
Comment
Question by:zachvaldez
  • 4
  • 2
  • 2
  • +3
11 Comments
 
LVL 75

Expert Comment

by:Aneesh Retnakaran
ID: 26138642
>How can only one be mailed?
You have to specify the addresse first ( first name + lname )
0
 
LVL 75

Expert Comment

by:käµfm³d 👽
ID: 26138755
You can pick one by using a DISTINCT clause. However, with this clause, you would lose the Fname column:
SELECT DISTINCT LName, Address FROM tblAddresses;

--Doing the following will still give you all 3 records
SELECT DISTINCT Fname, LName, Address FROM tblAddresses;

Open in new window

0
 
LVL 83

Expert Comment

by:leakim971
ID: 26138832
Hello zachvaldez,

Only ID should be unique.

Try :




SELECT c1.* FROM clients c1, (SELECT Address, MIN(ID) FROM clients GROUP BY Address) c2 WHERE c1.ID = c2.ID

Open in new window

0
Industry Leaders: We Want Your Opinion!

We value your feedback.

Take our survey and automatically be enter to win anyone of the following:
Yeti Cooler, Amazon eGift Card, and Movie eGift Card!

 
LVL 32

Expert Comment

by:awking00
ID: 26139190
What criteria should be used to determine the name to be used as the addressee (e.g. the lowest id)? Also, can there be John Smith and Jane Doe at the same address?
0
 
LVL 2

Expert Comment

by:mabbj747
ID: 26139257
The query below will give you the top 1 ID and the address

SELECT
   *
FROM
   yourTable
INNER JOIN
(
   SELECT
      MIN(ID)
      , Address
   FROM
      yourTable
   GROUP BY
      Address
)vwData
ON
   yourTable.ID = vwData.ID
0
 

Author Comment

by:zachvaldez
ID: 26162408
I was thinking of using it for mailing to ensure that only 1 can be mailed.
0
 
LVL 32

Expert Comment

by:awking00
ID: 26162488
>>I was thinking of using it for mailing to ensure that only 1 can be mailed.<<
That's fine, but what do you want to use to determine the addressee? Would you send to the smallest (or largest) id, the alphabetically first first_name-last_name combination, or something like resident or occupant?
0
 

Author Comment

by:zachvaldez
ID: 26183451
does this mean I have to add another field to determine which is to be mailed?
0
 
LVL 75

Expert Comment

by:käµfm³d 👽
ID: 26183613
Would it be sufficient to do something like below (for illustration only). Then, your address section would look like:

    Valued Customer
    123 Main St.
    Anywhere, US

Of course, you could change  "Valued Customer" to a text more fitting to your situation.
SELECT DISTINCT 'Valued Customer' as Name, Address FROM tblAddresses;

Open in new window

0
 
LVL 32

Expert Comment

by:awking00
ID: 26183741
>does this mean I have to add another field to determine which is to be mailed?< No. You just have to determine what you want on the name line of the mailing address. You could use something generic like resident, occupant, or valued customer that have been suggested or you could use the alphabetically first (or last) names or you could use the name line corresponding to the lowest (or highest) id, or perhaps something else. You just need to decide what the criteria should be.
0
 
LVL 32

Accepted Solution

by:
awking00 earned 1000 total points
ID: 26183856
See potential queries attached.
queries.txt
0

Featured Post

 [eBook] Windows Nano Server

Download this FREE eBook and learn all you need to get started with Windows Nano Server, including deployment options, remote management
and troubleshooting tips and tricks

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

This article explains how to reset the password of the sa account on a Microsoft SQL Server.  The steps in this article work in SQL 2005, 2008, 2008 R2, 2012, 2014 and 2016.
International Data Corporation (IDC) prognosticates that before the current the year gets over disbursing on IT framework products to be sent in cloud environs will be $37.1B.
In a question here at Experts Exchange (https://www.experts-exchange.com/questions/29062564/Adobe-acrobat-reader-DC.html), a member asked how to create a signature in Adobe Acrobat Reader DC (the free Reader product, not the paid, full Acrobat produ…
With just a little bit of  SQL and VBA, many doors open to cool things like synchronize a list box to display data relevant to other information on a form.  If you have never written code or looked at an SQL statement before, no problem! ...  give i…

834 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question