asked on

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

ASKER CERTIFIED SOLUTION

Raja Jegan R

membership

This solution is only available to members.

To access this solution, you must be a member of Experts Exchange.

Start Free Trial

Kevin Cross

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

SriChary

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.