[2 days left] What’s wrong with your cloud strategy? Learn why multicloud solutions matter with Nimble Storage.Register Now

x
?
Solved

Select distinctly using GROUP BY

Posted on 2004-08-16
5
Medium Priority
?
252 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
[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
  • 2
  • 2
5 Comments
 
LVL 17

Accepted Solution

by:
BillAn1 earned 600 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 70

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 70

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

On Demand Webinar: Networking for the Cloud Era

Did you know SD-WANs can improve network connectivity? Check out this webinar to learn how an SD-WAN simplified, one-click tool can help you migrate and manage data in the cloud.

Question has a verified solution.

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

In this article we will learn how to fix  “Cannot install SQL Server 2014 Service Pack 2: Unable to install windows installer msi file” error ?
An alternative to the "For XML" way of pivoting and concatenating result sets into strings, and an easy introduction to "common table expressions" (CTEs). Being someone who is always looking for alternatives to "work your data", I came across this …
Using examples as well as descriptions, and references to Books Online, show the documentation available for date manipulation functions and by using a select few of these functions, show how date based data can be manipulated with these functions.
Viewers will learn how the fundamental information of how to create a table.

656 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