Query in a Query.

[Kelemit]

I hope I can get some help on this. This is by far my weakest area in creating databases.

Queries:

I need a query that allows me to find the highest number in a field for each set in that table.

IE: I have a master table of classes, and a linked table (one to many) of revisions / versions of that specific class. I want to return ONLY THE MOST RECENT version of each class. I know how to show the top 1 return (showing descending order on version field), but that only returns the HIGHEST version for any ONE class. Any class that has only a single revision, or revisions lesser than the greatest will not be shown.

Anyway to be able to do this?

My idea at the moment is somthing along the lines of a query within a query, but i'm stumped at the moment on how to accomplish this.

Something like:

SELECT ClassTable.Classes, (Select TOP 1 VersionsTable.ClassVersion FROM VersionTable Order By VersionTable.ClassVersion DESC AS VersionList FROM ClassTable;

This query returns all the classes, but only the highest version of one specific class for ALL classes. (IE: Class1 has 6 revisions, 2 and 3 only have 2 Revisions, but Class 1, 2, and 3 all show that Revision 6 is the latest revisions for all 3 classes, where they should be returning 6 revisions for class1, and 2 revisions for classes 2 and 3.)

Any help would be great

Kelemit

 

[ByteMyzer]

What are your table structures? (table names, field names in each table)

 

[Kelemit]

Table structures

Table: Classes
ClassID (PK)
ClassNumber (text)
Defunct (Yes/No)

Table: ClassVersions
ClassID (Many to One to table Classes)
VersionID (PK)
Name
Hours
Classified
CourseType
HoursApplied
Knowledge
ValidTimeLength
Version (This is the field that IDs the Latest version. Higher the number
the more recent the version.)

The are linked 1 to Many through ClassID.


Other Tables:

Table: ClassesTaken (The classes that students have taken)
ClassesTakenID (PK)
VersionID
EmployeeID

Table: Employees
EmployeeID
Name
Address
Etc.

These three are linked through their appropriate IDs. Both ClassVersion and Employee are linked 1 to many to ClassesTaken.


Want to return ONLY the most recent version for EACH class listed in a drop down box. So was hoping for a query showing all classes, but only the latest version of that class, so that the VersionID number can be placed in the table that will keep track of what classes students have taken. VersionID is used because Class can be retrieved with it, as well knowing which version of that class the student took.

Kelemit.

 

[ByteMyzer]

If you want to return the ClassID and VersionID fields in your query, the following query/in-line query solution should give you what you need:

SELECT Classes.ClassID, ClassVersions.VersionID
FROM (Classes INNER JOIN ClassVersions ON Classes.ClassID=ClassVersions.ClassID)
INNER JOIN 
 (SELECT ClassID, Max(Version) AS xVersion
  FROM ClassVersions
  GROUP BY ClassID
 ) AS ClassVersions_1
ON ClassVersions.ClassID=ClassVersions_1.ClassID
AND ClassVersions.Version=ClassVersions_1.xVersion;