SQL Sub-query -- Select JobID with a maximum JobValue

Question

This seems like an easy thing, but I'm drawing a blank.

Select * from 
....
inner join 
  (
   select JobsID, Value from Jobs where Value **is the highest**
  ) as MaxJob on MaxJob.CustID = A.CustID

inner join
  (
   select other information based upon MaxJob.JobID
  ) as OtherStuff

Is there a nice way to have that first subquery give me the Job ID of the job with the maximum Value?

Thanks... this seems easy and I'm sure I'm overlooking something very elementary. One of those days...

Edit: Due to this question being a bit ambiguous, I've written up a much more detailed question here (since this question was answered correctly).

Answer 1

select max(JobId), value from jobs where...

Edit: my bad I misread the question, this might work

select jobid, max(value) from jobs....

Answer 2

If you want the single JobId with the highest Value:

SELECT JobId FROM Jobs WHERE Value = SELECT MAX(Value) FROM Jobs

But, that may give you multiple JobIds if thay all have the same Value. So, assuming you don't want that, I'd probably do:

SELECT MAX(JobId) as JobId FROM Jobs WHERE Value = SELECT MAX(Value) FROM Jobs

Answer 3

Yes, you're overlooking something: the excellent new language features in SQL Server 2005 and later! Particularly the analytic functions like row_number() and the very cool CROSS APPLY join.

row_number solves the first question (choosing "the highest" something for a given other thing):

with Ranked(a,b,c,d,rk) as (
  select T1.a,T2.b,T2.c,T2.d,
    row_number() over (
      partition by T1.a
      order by T2.x desc
    )
  from T1 join T2 on someCondition
)
  select a,b,c,d
  from Ranked
  where rk = 1;

CROSS APPLY solves the second question - creating a table source "based on MaxJob.JobID":

select *
from tableOrJoin
cross apply (
  select stuff
  from elsewhere
  where something = tableOrJoin.JobID
) as A

In other words, it allows you to have a "correlated join" by using a column value from the left hand table source in the definition of the right-hand table source.

If you have a specific question, please give more specific information. I suspect you may be able to use both of these new features in your solution.

Answer 4

Select top 1 JobId, Value from Jobs order by Value desc

This may result in a worse performance than max(Value), but it is less code

Answer 5

SELECT j.JOBID, MAX(Value) OVER(order by j.Value PARTITION by j.JOBID) as max_val, s.foobar FROM Jobs j INNER JOIN SomeOtherTable s ON (s.jobid = j.jobid) WHERE booya = win

I suspect that might make no sense, cause I don't know your tables :D

But LEARN THE POWER OF OVER AND PARTITION. LEARN IT, LOVE IT.