CODEWITHSUNDEEP
sqlsql-servermax
Kivayan
Kivayan

Reputation: 105

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

Upvotes: 4

Views: 249

Answers (4)

Saurabh Chandra Patel
Saurabh Chandra Patel

Reputation: 13586

SELECT * FROM tblTasks ORDER BY col DESC LIMIT 1, 1

Upvotes: 0

JamieD77
JamieD77

Reputation: 13949

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

Upvotes: 3

Gordon Linoff
Gordon Linoff

Reputation: 1269445

I would suggest DENSE_RANK(), if you want the second JobID:

SELECT tb.*
FROM (SELECT tb.*, DENSE_RANK() OVER (ORDER BY JobID DESC) as seqnum
      FROM tblTasks Tb
     ) tb
WHERE seqnum = 2;

If there are no duplicates, then OFFSET/FETCH is easier:

SELECT tb.*
from tblTasks
ORDER BY JobId
OFFSET 1
FETCH FIRST 1 ROW ONLY;

Upvotes: 2

Stanislovas Kalašnikovas
Stanislovas Kalašnikovas

Reputation: 9053

You can use syntax like this:

SELECT MAX(col)
FROM table
WHERE col < (SELECT MAX(col)
             FROM table)

Upvotes: 0

Related Questions