SQL Distinct

Hi,

I have 2 sql statements below. I want to select distinct rows based on columns "recipient" and "email". The first sql statment works fine. However i now also want to include the field "dateopened", but dont want this to select distinct rows based on dateopened. i.e i want have the first SQl statement but also include field "dateopened"

How can i acheieve this?

Hope that makes sense,

Thanks


1) "select distinct recipient, email from [Tracker]"
2)  "select distinct recipient, email, dateopened from [Tracker]"

Open in new window

coolispaulAsked:
Who is Participating?
I wear a lot of hats...

"The solutions and answers provided on Experts Exchange have been extremely helpful to me over the last few years. I wear a lot of hats - Developer, Database Administrator, Help Desk, etc., so I know a lot of things but not a lot about one thing. Experts Exchange gives me answers from people who do know a lot about one thing, in a easy to use platform." -Todd S.

Raja Jegan RSQL Server DBA & Architect, EE Solution GuideCommented:
Hope this helps:

select recipient, email, max(dateopened)
from [Tracker]
GROUP BY recipient, email

Use either Max or Min to bring the required records
0

Experts Exchange Solution brought to you by

Your issues matter to us.

Facing a tech roadblock? Get the help and guidance you need from experienced professionals who care. Ask your question anytime, anywhere, with no hassle.

Start your 7-day free trial
Kevin CrossChief Technology OfficerCommented:
You can use an aggregate function to get say MIN or MAX date.
select recipient, email
, min(dateopened) as min_dateopened
, max(dateopened) as max_dateopened
from [Tracker]
group by recipient, email

Open in new window

0
SriCharyCommented:
Hi,

If I understand it correctly you want two distinct fields while the third should not be a distinct column. If this is correct then I have not come across any such query which could give me such an output.

The reason being: While all the three columns are from the same table this is difficult as the Distinct statement will give unique values for the same table with selected columns. Any other column whether you want it to be distinct or not it will still give a distinct value as the sequence runs in that format.

You can display this through frontend code manipulation.

If anyone else has a better option I would be glad to know.
0
It's more than this solution.Get answers and train to solve all your tech problems - anytime, anywhere.Try it for free Edge Out The Competitionfor your dream job with proven skills and certifications.Get started today Stand Outas the employee with proven skills.Start learning today for free Move Your Career Forwardwith certification training in the latest technologies.Start your trial today
Query Syntax

From novice to tech pro — start learning today.

Question has a verified solution.

Are you are experiencing a similar issue? Get a personalized answer when you ask a related question.

Have a better answer? Share it in a comment.