CODEWITHSUNDEEP
sqlsql-server-2008-r2max
mhopkins321
mhopkins321

Reputation: 3073

Selecting several max() from a table

I will first say that the table structure is (unfortunately) set.

My goal is to select several max() from a query. Lets say I have the following tables

          jobReferenceTable                     jobList
 jobID | jobName  | jobDepartment |        listID |  jobID  |
_______|__________|_______________|        _______|_________|
   1   | dishes   |   cleaning    |          1    |    1    |
   2   |vacumming |   cleaning    |          2    |    5    |
   3   | mopping  |   cleaning    |          3    |    2    |
   4   |countMoney|     admin     |          4    |    4    |
   5   | hirePpl  |     admin     |          5    |    1    |
                                             6    |    2    |
                                             7    |    3    |
                                             8    |    3    |
                                             9    |    1    |
                                             10   |    5    |

Somehow, I would like to have a query that selects the jobID's from cleaning, and then shows the most recent jobList ID's for each job. I started a query below, and below that are what I'm hoping to get as results

query

SELECT jrt.jobName, jrt.jobDepartment
FROM jobReferenceTable
WHERE jobDepartment = 'cleaning'
JOIN jobList jl ON jr.jobID = jl.jobID

results

 jobName | jobDepartment | listID |
 ________|_______________|________|
    1    |    cleaning   |    9   |
    2    |    cleaning   |    6   |
    3    |    cleaning   |    8   |

Upvotes: 3

Views: 78

Answers (1)

William
William

Reputation: 6610

Try this;

SELECT jrt.jobName, jrt.jobDepartment, MAX(jl.listID)
FROM jobReferenceTable AS jrt INNER JOIN jobList AS jl ON jrt.jobID = jl.jobID
WHERE jrt.jobDepartment = 'cleaning'
GROUP BY jrt.jobName, jrt.jobDepartment

So far as I can see, you need only the one MAX() - the listID.

MAX() is an aggregate function, meaning that the rest of your result set must then be 'grouped'.

Upvotes: 2

Related Questions