Solved

SQL Distinct

Posted on 2009-07-15
3
607 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
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 59

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

Master Your Team's Linux and Cloud Stack!

The average business loses $13.5M per year to ineffective training (per 1,000 employees). Keep ahead of the competition and combine in-person quality with online cost and flexibility by training with Linux Academy.

Question has a verified solution.

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

Suggested Solutions

Title # Comments Views Activity
SQL Pivot Rows To Columns 10 55
T-SQL Query to include null values 3 32
Is there any way to convert exponential value to number in sql server 5 40
SSRS 2016 KPI 2 18
Hi, I have heard from my friends that it’s not possible to create Label Printing report using SSRS. I am amazed after hearing this words not possible in SSRS. I googled lot and found that it is possible to some of people know about the Report Bui…
Composite queries are used to retrieve the results from joining multiple queries after applying any filters. UNION, INTERSECT, MINUS, and UNION ALL are some of the operators used to get certain desired results.​
Email security requires an ever evolving service that stays up to date with counter-evolving threats. The Email Laundry perform Research and Development to ensure their email security service evolves faster than cyber criminals. We apply our Threat…
Although Jacob Bernoulli (1654-1705) has been credited as the creator of "Binomial Distribution Table", Gottfried Leibniz (1646-1716) did his dissertation on the subject in 1666; Leibniz you may recall is the co-inventor of "Calculus" and beat Isaac…

832 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