How to only get one entry per record when there are multiple entries? I just want the first entry per person.

Posted on 2008-02-08
Medium Priority
Last Modified: 2010-04-29
I am designing a report in SRS but need help with the following issue:

In my list of values I have 2004 records but only have 931 distinct names. My table looks like this:

name,    place                   day
smith      bathroom            12/04/2007
smith      bedroom              12/05/2007

What I need is to have only one smith come up with the most recent day to come up. Distinct wont work because i would get both entries. So I need one entry per name. I was thinking of using a self join but I could not figure out how to implement it.

Question by:richecker
LVL 19

Expert Comment

ID: 20852794
select * from tblname a left join b where a.name=b.name and some date functions
LVL 60

Expert Comment

ID: 20852807
select name, max(date) from table
group by name

Author Comment

ID: 20852917
I probably was not clear enough...

Here is my table:

first name      last name       address                 city                        dob
john               smith               111 e street          hicksville               01/01/1950
john               smith               111 e st                hicksville                01/01/1950

The address fields are similar but the names will come up twice when querying because of the two separate addresses. How do I only get one name and only one address to come up. Max didnt work.
Upgrade your Question Security!

Your question, your audience. Choose who sees your identity—and your question—with question security.


Author Comment

ID: 20852947
The left join did not seem to work either.
LVL 60

Accepted Solution

chapmandew earned 2000 total points
ID: 20852966
select name, max(address), max(date) from table
group by name
LVL 10

Expert Comment

ID: 20857001
distinct first name, last name , address,city,
group by first name, last name, address, city

But I am confused with your address
-      If the addresses in the DB are 111 e street and 111 e st then those are two distinct addresses, hence it will be count in to two rows.
-      If your table has the two addresses spelled in the above manner and still those are mean to be one address, then, it is a data issue, which is out of the scope that we are talking here.

 Any feedback will be appreciated.

Featured Post

[Webinar] Kill tickets & tabs using PowerShell

Are you tired of cycling through the same browser tabs everyday to close the same repetitive tickets? In this webinar JumpCloud will show how you can leverage RESTful APIs to build your own PowerShell modules to kill tickets & tabs using the PowerShell command Invoke-RestMethod.

Question has a verified solution.

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

A Stored Procedure in Microsoft SQL Server is a powerful feature that it can be used to execute the Data Manipulation Language (DML) or Data Definition Language (DDL). Depending on business requirements, a single Stored Procedure can return differe…
An alternative to the "For XML" way of pivoting and concatenating result sets into strings, and an easy introduction to "common table expressions" (CTEs). Being someone who is always looking for alternatives to "work your data", I came across this …
Via a live example, show how to setup several different housekeeping processes for a SQL Server.
SQL Database Recovery Software repairs the MDF & NDF Files, corrupted due to hardware related issues or software related errors. Provides preview of recovered database objects and allows saving in either MSSQL, CSV, HTML or XLS format. Ensures recov…

601 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