Solved

SQL Distinct

Posted on 2009-07-15
3
612 Views
Last Modified: 2012-05-07
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

0
Comment
Question by:coolispaul
[X]
Welcome to Experts Exchange

Add your voice to the tech community where 5M+ people just like you are talking about what matters.

  • Help others & share knowledge
  • Earn cash & points
  • Learn & ask questions
3 Comments
 
LVL 57

Accepted Solution

by:
Raja Jegan R earned 500 total points
ID: 24857753
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
 
LVL 60

Expert Comment

by:Kevin Cross
ID: 24857774
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
 

Expert Comment

by:SriChary
ID: 24857876
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

Featured Post

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!

Question has a verified solution.

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

If you find yourself in this situation “I have used SELECT DISTINCT but I’m getting duplicates” then I'm sorry to say you are using the wrong SQL technique as it only does one thing which is: produces whole rows that are unique. If the results you a…
In part one, we reviewed the prerequisites required for installing SQL Server vNext. In this part we will explore how to install Microsoft's SQL Server on Ubuntu 16.04.
In this brief tutorial Pawel from AdRem Software explains how you can quickly find out which services are running on your network, or what are the IP addresses of servers responsible for each service. Software used is freeware NetCrunch Tools (https…
Do you want to know how to make a graph with Microsoft Access? First, create a query with the data for the chart. Then make a blank form and add a chart control. This video also shows how to change what data is displayed on the graph as well as form…

628 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