Solved

Select distinctly using GROUP BY

Posted on 2004-08-16
5
233 Views
Last Modified: 2008-02-20
I am trying to remove duplicate rows from a query using GROUP BY and max but I dont seem to be having any joy.

I have 3 tables:

tblCourses:
keyCourse
Title

tblSubjects:
keySubject
Subject

tblCourses_Subjects:
keyFCourse
keyFSubject

tblCourses_Subjects is an allocation table allowing it to be a many-many relationship.

I now want to query this by selecting ALL course WHERE a boolean is set as per this query:

CREATE PROCEDURE Courses_GetFeaturedCourses
AS
SELECT DISTINCT max(tblCourses_Subjects.keyFSubject), tblSubjects.keySubject, tblSubjects.Subject, tblCourses.keyCourse, tblCourses.Title, tblCourses.Active FROM tblCourses INNER JOIN tblCourses_Subjects ON tblCourses.keyCourse = tblCourses_Subjects.keyFCourse INNER JOIN tblSubjects ON tblCourses_Subjects.keyFSubject = tblSubjects.keySubject
WHERE tblCourses.Active = 1 AND tblCourses.Featured = 1
GROUP BY tblSubjects.keySubject, tblSubjects.Subject, tblCourses.keyCourse, tblCourses.Title, tblCourses.Active
GO

The problem is that this return duplications for courses which are in multiple subjects. I thought I could get round this by using max() (on keyFSubject) and GROUP BY, but it still appears to be giving me duplications.

Output:
keySubject, keyCourse, Title, Subject

Output:
1, 1, Title, Subject
1, 1, Title, Subject
2, 2, Title, Subject
3, 8, Title, Subject
4, 12, Title, Subject

It is the SECOND row of output I'm trying to get rid of.

Many thanks


0
Comment
Question by:jonnyboy69
  • 2
  • 2
5 Comments
 
LVL 17

Accepted Solution

by:
BillAn1 earned 150 total points
ID: 11811014
You have MAX, but you are still including the subject  key without a max, and also the subject name as well
You could do as below, but it does have one problem - if a course is associated with more than 1 subject, it will return the maximum subject key, and the "maximum" subject name - you could have one subject with key 1, subject Zoology, and another record with key 99, subject Anatomy. Of both of htese subjects are part of the "Biology" course, then this query will return you a record with 99 Zoology Biology, since 99 is the max key, and Zoology is the max name, but of course 99 & Zoology don't go together.
Normally, if you want a distinct, it makes more sense to leave out altoghether any columns that have multiple values, particularly if they are text.

SELECT DISTINCT
    max(tblCourses_Subjects.keyFSubject),
    max(tblSubjects.Subject),
    tblCourses.keyCourse,
    tblCourses.Title,
    tblCourses.Active
FROM tblCourses
INNER JOIN
    tblCourses_Subjects
    ON tblCourses.keyCourse = tblCourses_Subjects.keyFCourse
INNER JOIN
    tblSubjects
    ON tblCourses_Subjects.keyFSubject = tblSubjects.keySubject
WHERE tblCourses.Active = 1
    AND tblCourses.Featured = 1
GROUP BY
    tblCourses.keyCourse,
    tblCourses.Title,
    tblCourses.Active
0
 

Author Comment

by:jonnyboy69
ID: 11811124
Nope thats the kiddie. I do note your point about max problems, however it doesnt actually matter what the subject values are in this case, as long as they are valid.
Thanks
0
 
LVL 69

Expert Comment

by:Scott Pletcher
ID: 11811324
I think the query below will do it, and return consistent/matching subject keys, course keys, titles and subjects.


SELECT uniqCourseSubject.keyFSubject AS KeySubject, tc.KeyCourse, tc.Title,
      (SELECT TOP 1 ts.Subject
      FROM tblSubjects ts
      WHERE uniqCourseSubject.keyFSubject = ts.keySubject) AS Subject
FROM tblCourses tc
INNER JOIN (
      SELECT keyFCourse, keyFSubject
      FROM tblCourses_Subjects tcs
      GROUP BY keyFCourse, keyFSubject
) AS uniqCourseSubject ON tc.keyCourse = uniqCourseSubject.keyFCourse
WHERE tc.Active = 1 AND tc.Featured = 1
0
 
LVL 17

Expert Comment

by:BillAn1
ID: 11811673
I think you need to modify that a bit, jonnyboy69 only wants one subject per course,

select ts.keyFSubject , tc.KeyCourse, tc.Title, ts.Subject
from tblCourses tc inner join
(
select keyFCourse, max(keyFSubject) keyFSubject
     from  tblCourses_Subjects tcs
     group by keyFCourse
) uq
on tc.keyCourse = uq.keyFCourse
inner join tblSubjects ts
on ts.keySubject = uq.keyFSubject
0
 
LVL 69

Expert Comment

by:Scott Pletcher
ID: 11811937
D'OH:

Yes, I meant to do that (actually I was planning to use MIN(), but MAX() is good too :-) ).  Sorry, don't have any data to test with so it's to overlook things.
0

Featured Post

Free Tool: ZipGrep

ZipGrep is a utility that can list and search zip (.war, .ear, .jar, etc) archives for text patterns, without the need to extract the archive's contents.

One of a set of tools we're offering as a way to say thank you for being a part of the community.

Question has a verified solution.

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

Load balancing is the method of dividing the total amount of work performed by one computer between two or more computers. Its aim is to get more work done in the same amount of time, ensuring that all the users get served faster.
Ever needed a SQL 2008 Database replicated/mirrored/log shipped on another server but you can't take the downtime inflicted by initial snapshot or disconnect while T-logs are restored or mirror applied? You can use SQL Server Initialize from Backup…
Via a live example, show how to shrink a transaction log file down to a reasonable size.
Using examples as well as descriptions, and references to Books Online, show the documentation available for datatypes, explain the available data types and show how data can be passed into and out of variables.

733 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