Select distinct records from Join statement

On the following query I am looking for distinct records.  Many of the records are duplicates.  How can I select only the unique, distinct records.  The whole row needs to be compared to other rows.  I am having a brain fart.  Would it be by adding group by H.Agency, H.ActivityDate, H.Classification,H.BasedOn, H.HourTimeFrom, H.HourTimeTo, H.Hours, H.Duration, .Frequency, H.ActivityType, H.ActivityOther,  H.Objectives, H.Strategy, H.Need, H.NeedOther, H.Outcome, A.ActivityName, H.Narrative, H.EntryTime, H.Fiscal, H.Unregistered
at the end?

SELECT H.Agency, H.ActivityDate, H.Classification,H.BasedOn, H.HourTimeFrom,
 H.HourTimeTo, H.Hours, H.Duration, H.Frequency, H.ActivityType, H.ActivityOther,
  H.Objectives, H.Strategy, H.Need, H.NeedOther,
 H.Outcome, A.ActivityName, H.Narrative, H.EntryTime, H.Fiscal, H.Unregistered
FROM tblOrgHours H 
	INNER JOIN tblOrgActivities A 
		ON H.ActivityID = A.ActivityID 
WHERE H.Unregistered > 0

Open in new window

al4629740Asked:
Who is Participating?
 
Raja Jegan RConnect With a Mentor SQL Server DBA & ArchitectCommented:
Yes, you can but you need to put all the columns in SELECT clause without aggregation like this..
SELECT H.Agency, H.ActivityDate, H.Classification,H.BasedOn, H.HourTimeFrom,
 H.HourTimeTo, H.Hours, H.Duration, H.Frequency, H.ActivityType, H.ActivityOther,
  H.Objectives, H.Strategy, H.Need, H.NeedOther,
 H.Outcome, A.ActivityName, H.Narrative, H.EntryTime, H.Fiscal, H.Unregistered
FROM tblOrgHours H 
	INNER JOIN tblOrgActivities A 
		ON H.ActivityID = A.ActivityID 
WHERE H.Unregistered > 0
GROUP BY H.Agency, H.ActivityDate, H.Classification,H.BasedOn, H.HourTimeFrom,
 H.HourTimeTo, H.Hours, H.Duration, H.Frequency, H.ActivityType, H.ActivityOther,
  H.Objectives, H.Strategy, H.Need, H.NeedOther,
 H.Outcome, A.ActivityName, H.Narrative, H.EntryTime, H.Fiscal, H.Unregistered

Open in new window

0
 
Raja Jegan RSQL Server DBA & ArchitectCommented:
you can use DISTINCT to get unique records. If you want to get unique records at selected column level, then provide us some sample data and expected data to help you out..
SELECT DISTINCT H.Agency, H.ActivityDate, H.Classification,H.BasedOn, H.HourTimeFrom,
 H.HourTimeTo, H.Hours, H.Duration, H.Frequency, H.ActivityType, H.ActivityOther,
  H.Objectives, H.Strategy, H.Need, H.NeedOther,
 H.Outcome, A.ActivityName, H.Narrative, H.EntryTime, H.Fiscal, H.Unregistered
FROM tblOrgHours H 
	INNER JOIN tblOrgActivities A 
		ON H.ActivityID = A.ActivityID 
WHERE H.Unregistered > 0

Open in new window

0
 
al4629740Author Commented:
can I use group by?
0
Cloud Class® Course: C++ 11 Fundamentals

This course will introduce you to C++ 11 and teach you about syntax fundamentals.

 
al4629740Author Commented:
And that will get the same results?

What does without aggregation mean?
0
 
Mark WillsTopic AdvisorCommented:
select distinct <data>

is equivalent to

select <data>
group by <data>

it mostly depends on the where clause and joins as to how / allowing SQL optimizer to exclude rows from the comparison.

Essentially, DISTINCT will gather all information, do any cast or converts (etc) and then decide DISTINCT
Group By can (not always) filter out some data before having to decide the result set.

If you look at the logical processing order of execution :
1. FROM
2. ON
3. OUTER
4. WHERE
5. GROUP BY
6. CUBE | ROLLUP
7. HAVING
8. SELECT
9. DISTINCT
10 ORDER BY
11. TOP

You see DISTINCT happening late in the cycle - so - if you cant eliminate unwanted information first, it has a potential truckload of work to decide distinct.

In your example, there is not much difference and there are no aggregations - so DISTINCT is probably fine

if ever in doubt, eliminate the unwanted stuff first with a CTE
with cte as
(select <data> where conditions and manipulations occur and exclusions )
select distinct <refined data>
from CTE

aggregation means you want to use SUM() or COUNT() an element not contained in your group by, and distinct wont allow aggregations per se - that is when you need to use grouping (and grouping can include window functions using the OVER() clause)

but you have already selected your answer :) I just wish I remembered to hit submit when I started answering....
0
 
Raja Jegan RSQL Server DBA & ArchitectCommented:
>> What does without aggregation mean?

As Mark mentioned above, Aggregation operations refers to Min(), MAX(), SUM(), COUNT(), etc., Since your query doesn't have any of the aggregations, you can use either DISTINCT or GROUP BY. If you have aggregations, then you might need to use GROUP BY only..
0
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.

All Courses

From novice to tech pro — start learning today.