CODEWITHSUNDEEP
sqldelphims-access-2007
Romans
Romans

Reputation: 485

How to get MAX() value and Record Primary Key where the value occurs in a Database?

I have a database with Hours as integer field and PK like record ID/Autonumber in a table to identify records. How can I with an SQL Query get the value max value and the record ID or field value where the max value is located? I am using a .mdb MS Access database.

Code I am currently using:

        with dmpat.ADOQuery1 do
        begin
          sql.Clear; //Hours is integer field
          //Here I would like to get the ID (integer) field value of each of these values:
          sql.Add('SELECT MAX(Hours) AS [Max], MIN(Hours) AS [Min], AVG(Hours) AS [AVE]');
          sql.Add('FROM Professions');
          open;

          Max := fields[0];
          Min := fields[1];
          Ave := fields[2];
        end;

Is such a thing possible? New to SQL, thanks anyway!

Upvotes: 0

Views: 3199

Answers (2)

Romans
Romans

Reputation: 485

So I found this solution. This would give the Primary Key or the record ID. It also allows for the possibility that there is more than one record with the MAX() or MIN() value.

SELECT ID, Name, Hours AS [Max] FROM Professions
    WHERE Hours = (SELECT MAX(Hours) FROM Professions)
    ORDER BY ID ASC

Here is proof using another database, though the same concept applies. enter image description here

I actually wrote this code while working on a completely different problem.

Upvotes: -2

forpas
forpas

Reputation: 164064

You can use 2 subqueries:

SELECT MAX(Hours) AS [Max],
       (SELECT TOP 1 ID FROM Professions ORDER BY Hours DESC) AS Max_ID, 
       MIN(Hours) AS [Min], 
       (SELECT TOP 1 ID FROM Professions ORDER BY Hours) AS Min_ID,
       AVG(Hours) AS [AVE]
FROM Professions

Each of the subqueries sorts the table either descending or ascending and picks the top row.

Upvotes: 3

Related Questions