Hey guys, I have this query which tries to get the maximum values from a table.
Code Snippet
SELECT scbcrse_subj_code,
MAX(scbcrse_eff_term), scbcrse_crse_numb, scbcrse_coll_code, scbcrse_title, scbcrse_csta_code
FROM Courses
WHERE scbcrse_csta_code = ''A''
GROUP BY scbcrse_subj_code, SCBCRSE_CRSE_NUMB, scbcrse_coll_code, scbcrse_csta_code, scbcrse_title
ORDER BY scbcrse_subj_code
Sample Table
Now, there is a column which is called scbcrse_title which shows the title of the course, which in this case on the table the titles are different. One is called Advanced Accounting and the other is called Financial Accounting IV. My question is, how can I only show the highest number on the scbcrse_eff_term? The problem occurs when there are different titles.
Does scbcrrse_title need to be part of the group by? Can you just group on crse_numb, coll_code, and subj_code only ? Then subselect the title using the three group by values where eff_term is max? Something like the below (i did not check the code for errors sorry)
Code Snippet
SELECT C.scbcrse_subj_code,
C.scbcrse_crse_numb,
C.scbcrse_coll_code,
C.scbcrse_csta_code,
MAX(C.scbcrse_eff_term),
(SELECT scbcrse_title
FROM Courses
WHERE scbcrse_subj_code = C.scbcrse_subj_code
AND scbcrse_crse_numb = C.scbcrse_crse_numb
AND scbcrse_coll_code = C.scbcrse_coll_code
AND scbcrse_csta_code = C.scbcrse_csta_code
AND scbcrse_eff_term) = (SELECT MAX(C.scbcrse_eff_term) FROM Courses WHERE C.Cscbcrse_csta_code = 'A' GROUP BY C.scbcrse_subj_code, C.SCBCRSE_CRSE_NUMB, C.scbcrse_coll_code, C.scbcrse_csta_code ) AS [Title]
FROM Courses C
WHERE C.Cscbcrse_csta_code = 'A'
GROUP BY C.scbcrse_subj_code, C.SCBCRSE_CRSE_NUMB, C.scbcrse_coll_code, C.scbcrse_csta_code
ORDER BY scbcrse_subj_code
|||Thanks Dave, I'll check the code to see if it works. And yes, the GROUP BY command does force me to include scbcrse_title.
|||Maybe this:
Code Snippet
SELECT scbcrse_subj_code,
scbcrse_eff_term,
scbcrse_crse_numb,
scbcrse_coll_code,
scbcrse_title,
scbcrse_csta_code
FROM Courses c
inner join
(
SELECT scbcrse_subj_code,
scbcrse_crse_numb,
scbcrse_coll_code,
MAX(scbcrse_eff_term) as eff_term
FROM Courses
) selhigh
on c.scbcrse_subj_code = selhigh.scbcrse_subj_code
and c.scbcrse_crse_numb = selhigh.scbcrse_crse_numb
and c.scbcrse_coll_code = selhigh.scbcrse_coll_code
and c.scbcrse_eff_term = selhigh.scbcrse_eff_term
WHERE c.scbcrse_csta_code = 'A'
|||Diango,You can do this without a GROUP BY as follows:
SQL Server 2000
SELECT scbcrse_subj_code, scbcrse_eff_term, scbcrse_crse_numb, scbcrse_coll_code, scbcrse_title, scbcrse_csta_code
FROM Courses
WHERE scbcrse_csta_code = ''A''
AND NOT EXISTS (
SELECT * FROM Courses AS C
WHERE C.scbcrse_subj_code = Courses.scbcrse_subj_code
AND C.scbcrse_crse_numb = Courses.scbcrse_crse_numb
AND C.scbcrse_coll_code = Courses.scbcrse_coll_code
AND C.scbcrse_scbcrse_eff_term > Courses.scbcrse_eff_term
)
ORDER BY scbcrse_subj_code
In other words, choose all rows for Courses where the table does not contain a later (measured by eff_term) row with the same (subj_code, crse_numb, coll_code) combination. This assumes you want one result row for each (subj_code, crse_numb, coll_code) combination, and you can adjust the inner WHERE clause if this is not the set of columns that you want one row for each combination of.
In SQL Server 2005, you have another option:
SQL Server 2005
WITH Courses_ranked AS (
SELECT
scbcrse_subj_code, scbcrse_eff_term, scbcrse_crse_numb,
scbcrse_coll_code, scbcrse_title, scbcrse_csta_code,
RANK() OVER (
PARTITION BY scbcrse_subj_code, scbcrse_crse_numb, scbcrse_coll_code
ORDER BY scbcrse_eff_term DESC
) AS rk
)
SELECT
scbcrse_subj_code, scbcrse_eff_term, scbcrse_crse_numb,
scbcrse_coll_code, scbcrse_title, scbcrse_csta_code
FROM Courses_ranked
WHERE rk = 1
Both solutions will return the "latest" row (or rows in the case of ties for latest) for each combination (scbcrse_subj_code, scbcrse_crse_numb, scbcrse_coll_code) in the table.
Steve Kass
Drew University
http://www.stevekass.com
|||The first solution almost worked but it had the problem that when there was a code that only had one value, it got omitted. The second solution I have never done before so I'm having some problems implementing it.|||I notice I left the = 'A' out of the inner query in the first solution. That could be it, but it could also be something about your data that you didn't mention. If you post some sample data and your adapted query that fails, I can take a look.
SK
|||That worked, you rock! As soon as I added the ''A'' in the inner join, it came back with the correct result.|||Steve, I don't mean to be a pain in the ass, but I have a new issue. I'm still a bit of a noob so bare with me. Your code worked great by the way, it did the job as intended. New update is required which I wasn't aware. I want to take into account those courses that do have '' I '' as well as the maximum value on the scbcrse_eff_term. I can do this easily by simply removing the WHERE clause where scbcrse_csta_code = ''A'' on both occasions. The thing is, for those courses that have the maximum value which have an '' I '' I need those courses removed completly. ( I don't mean delete those records that '' I '' , I just mean Omitting them some how.|||I hope I'm understanding, because you didn't give any specific example.
You want to see the latest row for each (subject code, course number, college code) from among those rows with csta code either 'A' or 'I', only if that latest row happens to be one of the 'A' rows. Note that if 'A' and 'I' are the only possible values of csta_code, you don't have to say WHERE scbcrse_csta_code IN ('A','I'). I think this will do it.
SK
SELECT scbcrse_subj_code, scbcrse_eff_term, scbcrse_crse_numb, scbcrse_coll_code, scbcrse_title, scbcrse_csta_code
FROM Courses
WHERE scbcrse_csta_code IN ('A','I')
AND NOT EXISTS (
SELECT * FROM Courses AS C
WHERE C.scbcrse_subj_code = Courses.scbcrse_subj_code
AND C.scbcrse_crse_numb = Courses.scbcrse_crse_numb
AND C.scbcrse_coll_code = Courses.scbcrse_coll_code
AND C.scbcrse_scbcrse_eff_term > Courses.scbcrse_eff_term
AND C.scbcrse_csta_code IN ('A','I')
)
AND Courses.scbcrse_csta_code = 'A'
ORDER BY scbcrse_subj_code
|||
Sorry, I should have explained myself a little better. Ok, this is your code:
SELECT scbcrse_subj_code, scbcrse_eff_term, scbcrse_crse_numb, scbcrse_coll_code, scbcrse_title, scbcrse_csta_code
FROM Courses
WHERE scbcrse_csta_code = ''A''
AND NOT EXISTS (
SELECT * FROM Courses AS C
WHERE C.scbcrse_subj_code = Courses.scbcrse_subj_code
AND C.scbcrse_crse_numb = Courses.scbcrse_crse_numb
AND C.scbcrse_Coll_code = Courses.scbcrse_Coll_Code
AND C.scbcrse_csta_code = ''A''
AND C.scbcrse_scbcrse_eff_term > Courses.scbcrse_eff_term
)
ORDER BY scbcrse_subj_code
From the original code, we had a clause WHERE = ''A''. By having those two clauses, it returned the top value from scbcrse_eff_term Where scbcrse_csta_code = A, agreed? I found out later that the original requirement was wrong. The correct requirement was, get the highest scbcrse_eff_term as long as the class is active, or otherwise known as having a csta_code of ''A''. I know it sounds the same but let me explain further.
For example:
scbcrse_subj_code scbcrse_crse_numb scbcrse_eff_term scbcrse_csta_code
ACCT 4041 195600 A
ACCT 4041 200023 A
ACCT 4041 221457 I
From the original code, it should return the second row with a value of 200023 because it's the highest row that has a csta_code of ''A''. Here is where it changes, on the example, since the highest value for the class is 221457 and has a csta_code of '' I '' the class has become inactive and therefore ACCT 4041 must no longer show up in our query. So in essence, every class that has the highest scbcrse_eff_term with a value of csta_code '' I '' should not show up at all in our query return. So ACCT 4041 would not show up on our list.
|||
Code Snippet
SELECT scbcrse_subj_code,
scbcrse_eff_term,
scbcrse_crse_numb,
scbcrse_coll_code,
scbcrse_title,
scbcrse_csta_code
FROM Courses c
inner join
(
SELECT scbcrse_subj_code,
scbcrse_crse_numb,
scbcrse_coll_code,
MAX(scbcrse_eff_term) as scbcrse_eff_term
FROM Courses
) selhigh
on c.scbcrse_subj_code = selhigh.scbcrse_subj_code
and c.scbcrse_crse_numb = selhigh.scbcrse_crse_numb
and c.scbcrse_coll_code = selhigh.scbcrse_coll_code
and c.scbcrse_eff_term = selhigh.scbcrse_eff_term
WHERE c.scbcrse_csta_code = 'A'
|||Dale, that's not working for me. I'm getting some errors when I try to implement it. I get the error of it's not a group by function. I also think that the code would return the maximum value of the courses that have a csta_code value of ''A'', not taking into account '' I ''. Which I do want to take into account '' I '' , but if the maximum scbcrse_eff_term of a course has a csta_code of '' I '' the class should be omitted completely from the result. It's not just eliminating all the '' I '', but it's eliminating all the courses from the list that has a maximum scbcrse_eff_term with scbcrse_csta_code of '' I ''. I don't know if that makes sense to you.|||See if this is better.
I forgot the group by in the subquery.
This will gather up all the courses with the most current date, then only keep the ones that are A.
My understanding from what you've been saying is that you want to disregard all entries for the class if its current row is an I.
This should do just that.
Code Snippet
SELECT scbcrse_subj_code,
scbcrse_eff_term,
scbcrse_crse_numb,
scbcrse_coll_code,
scbcrse_title,
scbcrse_csta_code
FROM Courses c
inner join
(
SELECT scbcrse_subj_code,
scbcrse_crse_numb,
scbcrse_coll_code,
MAX(scbcrse_eff_term) as scbcrse_eff_term
FROM Courses
GROUP BY scbcrse_subj_code,
scbcrse_crse_numb,
scbcrse_coll_code
) selhigh
on c.scbcrse_subj_code = selhigh.scbcrse_subj_code
and c.scbcrse_crse_numb = selhigh.scbcrse_crse_numb
and c.scbcrse_coll_code = selhigh.scbcrse_coll_code
and c.scbcrse_eff_term = selhigh.scbcrse_eff_term
WHERE c.scbcrse_csta_code = 'A'
|||It doesn't bring back the desired result. Let me show you what I mean.
scbcrse_subj_code scbcrse_crse_numb scbcrse_eff_term scbcrse_csta_code
ACCT 4041 195600 A
ACCT 4041 200023 A
ACCT 4041 221457 I
This is an example of a class that has gone inactive. This table has been poorly designed, which is why it's so challenging to create the proper effect. As you can see from this class, it has become inactive. When we run your query, it returns for example ACCT 4041 200023 and a code of A. The desired result is that this course doesn't come back at all because the maximum value for it is 221457 and because it has a scbcrse_csta_code of '' I ''.
Steve's code is great because it really can eliminate duplicates and it also brings the highest value of a course no matter what csta_code it has, because I removed the two WHERE clauses. Now, from the result set, I need to remove the courses that have a maximum value with a csta_code of '' I '' combination.
