Would like help with UGLY query.

Please start any new threads on our new site at https://forums.sqlteam.com. We've got lots of great SQL Server experts to answer whatever question you can come up with.

All Forums

SQL Server 2000 Forums

SQL Server Development (2000)

Would like help with UGLY query.

Author

Topic

madrak
Starting Member

20 Posts

Posted - 2009-01-26 : 13:05:54

I have two tables set up like the following
Student_Record
institution_id
course_number
course_name

Institution_Record
institution_id
course_number

and the data I want out is:

institution_id
course_number
course_name

Now, because of the way the system is set up (3rd party software and I don't have the ability to make changes) the course names are entered into the student record off of their transcript, so the same course may have several different names (Accounting 101 might be Principles of Accounting 1 or PRINC ACCT 1, etc...)

To resolve this and hopefully provide useful information, the course_name that is in the result set should be the most frequently appearing name for that course.

I have tried numerous different ways to achieve this, a correlated subquery, joining to a temp table... "inner join (select foo from bar where...) results on" etc.. Does anyone have any suggestions on the best way to do this (keeping in mind a database redesign is not possible).

Any help would be appreciated!

-madrak

madrak
Starting Member

20 Posts

Posted - 2009-01-26 : 15:26:42

Fortunately me for me this was actually on an sql server 2005 installation and I was able to get it to work with Row_Number/Over and Partitioning, but I do some work on sql server 2000 and does anyone have any ideas on how to do the same thing on a 2000 installation?

Ifor
Aged Yak Warrior

700 Posts

Posted - 2009-01-27 : 12:37:25

[code]-- *** Test Data ***
CREATE TABLE #Student_Record
(
institution_id int NOT NULL
,course_number int NOT NULL
,course_name varchar(50) NOT NULL
)
INSERT INTO #Student_Record
SELECT 1, 1, 'Accounting 101' UNION ALL
SELECT 1, 1, 'Accounting 101' UNION ALL
SELECT 1, 1, 'Principles of Accounting 1' UNION ALL
SELECT 1, 1, 'Principles of Accounting 1' UNION ALL
SELECT 1, 1, 'PRINC ACCT 1' UNION ALL
SELECT 1, 2, 'Accounting 102' UNION ALL
SELECT 1, 2, 'Accounting 102' UNION ALL
SELECT 1, 2, 'Principles of Accounting 2' UNION ALL
SELECT 1, 2, 'PRINC ACCT 2'
-- *** End Test Data ***

CREATE TABLE #Occurs
(
institution_id int NOT NULL
,course_number int NOT NULL
,course_name varchar(50) NOT NULL
,Occurs int NOT NULL
,PRIMARY KEY CLUSTERED (institution_id, course_number, course_name)
)

INSERT INTO #Occurs
SELECT institution_id, course_number, course_name
,COUNT(*) AS Occurs
FROM #Student_Record
GROUP BY institution_id, course_number, course_name

SELECT institution_id, course_number
-- choose a rule for ties
,MIN(course_name) AS course_nameMin
,MAX(course_name) AS course_nameMax
FROM #Occurs O
WHERE EXISTS
(
SELECT NULL
FROM
(
SELECT institution_id, course_number, MAX(O1.Occurs) AS Occurs
FROM #Occurs O1
GROUP BY institution_id, course_number
) D1
WHERE D1.institution_id = O.institution_id
AND D1.course_number = O.course_number
AND D1.Occurs = O.Occurs
)
GROUP BY institution_id, course_number[/code]

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources