Select 1 row from MANY

I just need one of the job names!

I was able to get the first version working, but I'm more interested in the second solution you offered. I'm familiar with aggregates using group by, but I have no idea what the over(partition by) does!!

Is there any benefits to one over the other?
If it matters, my actual query is more involved. This is the inner query which uses multiple joins, which is then pivoted.

Performance wise, I don't know.  But, logically, the partition by pattern is not aggregating; it is numbering within a group (partition) and then choosing. For example, you could identify the second highest salary which you cannot do with max().  The real nice thing about the partition by construct is that identifies the row of interest.  Unlike max(), for example, which requires a clumsy nested, correlated subuery in the where clause to identify the row containing the max value.  The equivalent partion by condition is trivial--> where seq = 1.

Get familiar with partition by, you will find is solves many thorny query problems and you will soon be living in despair when you need to work on a database that doesn't support it. :>)

 

I used the first option, worked like a charm. Will look into the second option.