CODEWITHSUNDEEP
mysqlgreatest-n-per-group
BluGeni
BluGeni

Reputation: 3454

MYSQL having multiple Selects that corresond with different where conditions

I want to execute one mysql statement where I have a select that is not part of the where condition. this is my code:

    SELECT oil.empid
     , oil.mileage
     , oil.`date`
     , max(oil.mileage) AS expr1

FROM
  oil
WHERE
  oil.mileage < (SELECT max(oil.mileage) AS expr1
                 FROM
                   oil)

GROUP BY
  oil.empid
ORDER BY
  oil.mileage

now this selects the second largest mileage but what i want is both the second and largest mileage. I believe that i just need to select max(mileage) outside of the WHERE but im not sure how to do that. Thanks for any help

edit: If the data entered for these 1497 was

mileage: 100 , 100000 , and 200000

and for empid 2000

mileage: 100 , 1500 , 5000

I would want this:

 empid|2nd max  | max
 1497 | 100000  |  200000
 2000 | 1500    |  5000

Upvotes: 0

Views: 82

Answers (2)

Brian Driscoll
Brian Driscoll

Reputation: 19635

This may be a naive response, but why not just do this:

SELECT oil.empid
      , oil.mileage
      , oil.`date`
      , max(oil.mileage) as expr1
FROM oil
GROUP BY oil.empid, oil.`date`
ORDER BY oil.mileage DESC
LIMIT 2

UPDATE
Given OP's update it's clear that we need an entirely different sort of query, something like this:

SELECT outer.empid
       , (SELECT first.mileage
          FROM oil as first
          WHERE first.empid = outer.empid
          ORDER BY first.mileage DESC
          LIMIT 1) AS max
       , (SELECT second.mileage
          FROM oil as second
          WHERE second.empid = outer.empid
          ORDER BY second.mileage DESC
          LIMIT 1
          OFFSET 1) as 2nd_max
FROM oil as outer

Upvotes: 1

ypercubeᵀᴹ
ypercubeᵀᴹ

Reputation: 115530

You can use LIMIT with OFFSET in MySQL's ORDER BY clause to find the second mileage. This way you gain by not using the expensive GROUP BY.

(Update) after the clarifications by the OP:

SELECT oil.empid
     , oil.mileage
     , oil.date
FROM
        oil
    JOIN
        ( SELECT DISTINCT empid
          FROM oil
        ) AS emp
            ON  oil.empid = emp.empid
            AND oil.mileage >= 
                ( SELECT mileage
                  FROM oil AS o
                  WHERE o.empid = emp.empid
                  ORDER BY mileage DESC
                  LIMIT 1  OFFSET 1
                )
ORDER BY
    oil.empid
  , oil.mileage               --- ASC or DESC here, your choice
;

Upvotes: 1

Related Questions