Select second MAX value

Question

I want to select second highest value from tblTasks(JobID, ItemName, ContentTypeID)

That's what I though of. I bet it can be done easier but I don't know how.

SELECT Max(JobID)    AS maxjobid,
       Max(ItemName) AS maxitemname,
       ContentTypeID
FROM   
      (SELECT JobID, ItemName, ContentTypeID
      FROM tblTasks Ta
      WHERE JobID NOT IN
            (SELECT MAX(JobID)
            FROM tblTasks Tb
            GROUP BY ContentTypeID)
            ) secmax

     GROUP BY secmax.ContentTypeID

Answer 1

I'm guessing you'd want something like this.

SELECT  JobID AS maxjobid,
        ItemName AS maxitemname,
        ContentTypeID
FROM    (SELECT JobID,
                ItemName,
                ContentTypeID,
                ROW_NUMBER() OVER (PARTITION BY ContentTypeID ORDER BY JobID DESC) Rn
         FROM   tblTasks Ta

        ) t
WHERE Rn = 2

this would give you the second highest JobID record per ContentTypeID