Kivayan Kivayan - 6 months ago 21
SQL Question

Select second MAX value

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

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