MySQL optimized query

MySQL

Now I came across an issue, I have table as below:

Task Id       StudentId    Marks
  1                1         12
  1                2         15              
  1                3         17
  2                1         15 
  2                3         20
  3                1         8 
  3                3         5         
  3                5         17

I was required to get a lists of studentid who has max and marks in each task. So the required output is:

TaskId StudentId Marks

 1                    3            17
 2                    3            20
 3                    5            17

Now I created a temporary table to store max.marks of each task.

TaskId Max.Marks

 1     17
 2     20
 3     17

Then I joined the temporary table to the main table and fetched the row with marks equal to max.marks of that task.

Can anyone suggest a better query without any sub-queries and temporary table?

Thanks

Best Answer

Instead of temporary table, use a subquery (a derived table), then join it to the original marks table:

SELECT
    t.TaskId, t.StudentId, t.Marks 
FROM
        tableX AS t
    JOIN 
        ( SELECT 
              TaskID
            , MAX(Marks) AS MaxMarks
          FROM 
              tableX
          GROUP BY
              TaskID
        ) AS m
            ON  m.TaskId = t.TaskId
            AND m.MaxMarks = t.Marks ;

If you have an index on (TaskId, Marks, StudentId), it will help efficiency.

You can also use this variation:

SELECT
    t.TaskId, t.StudentId, t.Marks 
FROM
        ( SELECT DISTINCT
              TaskId
          FROM
              tableX
        ) AS dt
    JOIN 
        tableX AS t
            ON  t.TaskId = dt.TaskId
            AND t.Marks = 
                ( SELECT 
                      tm.Marks 
                  FROM 
                      tableX AS tm
                  WHERE
                      tm.TaskId = dt.TaskId
                  ORDER BY 
                      tm.Marks DESC
                  LIMIT 1
                ) ;

Related Solutions

Mysql – Selecting minimum value using a subquery

Modifying slightly your second query, will give you both the merchant id and the lowest price (over all products that pass the conditions - I guess that's what you want):

SELECT p.p_m_id, MIN(p_price) AS min_p_price 
FROM tgmp_affiliates ga 
JOIN tgmp_prices p 
    ON ga.a_code = p.p_gtin 
        AND ga.a_code > '' 
JOIN tgmp_merchants m 
    ON m.m_id = p.p_m_id 
WHERE ga.site_id = '34' 
    AND p.site_id = '34' 
    AND ga.a_parent = '25573' 
    AND p.p_type = 'games' 
    AND m.m_hide = 0 
GROUP BY p.p_m_id ;

Then you can join this - as a derived table - to all the tables that you need data from in the results:

SELECT
    m.*, p.*, ga.*                     -- whatever columns you want  
FROM tgmp_affiliates ga 
JOIN tgmp_prices p 
    ON ga.a_code = p.p_gtin 
        AND ga.a_code > '' 
JOIN tgmp_merchants m 
    ON m.m_id = p.p_m_id 
JOIN
      ( SELECT p.p_m_id, MIN(p_price) AS p_price 
        FROM tgmp_affiliates ga 
        JOIN tgmp_prices p 
            ON ga.a_code = p.p_gtin 
                AND ga.a_code > '' 
        JOIN tgmp_merchants m 
            ON m.m_id = p.p_m_id 
        WHERE ga.site_id = '34' 
            AND p.site_id = '34' 
            AND ga.a_parent = '25573' 
            AND p.p_type = 'games' 
            AND m.m_hide = 0 
        GROUP BY p.p_m_id 
      ) AS tmp
    ON  tmp.p_m_id = p.p_m_id 
    AND tmp.p_price = p.p_price
WHERE ga.site_id = '34' 
    AND p.site_id = '34' 
    AND ga.a_parent = '25573' 
    AND p.p_type = 'games' 
ORDER BY p.p_price ;

MySQL getting result using 3 Tables and case statements

MySQL does not support full outer joins, so you will have to use unions:

select phno,
if(count(phno1) = 0, 'No', 'Yes') as person,
if(count(phno2) = 0, 'No', 'Yes') as person2,
if(count(phno3) = 0, 'No', 'Yes') as person3,
from (select phno, phno as phno1, NULL as phno2 , NULL as phno3 from person  
      union
      select phno, NULL as phno1, phno as phno2 , NULL as phno3 from person2   
      union
      select phno, NULL as phno1, NULL as phno2 , phno as phno3 from person3
) sub
group by phno

If you only want to check for a single person, you can use Aditys code slight modifications:

SELECT DISTINCT p.phno AS 'phno',
(CASE WHEN ISNULL(person.phno) THEN 'NO' ELSE 'Yes' END) AS 'person',
(CASE WHEN ISNULL(person2.phno) THEN 'NO' ELSE 'Yes' END) AS 'person2',
(CASE WHEN ISNULL(person3.phno) THEN 'NO' ELSE 'Yes' END ) AS 'person3'
FROM (SELECT 4567 AS phno) p
LEFT JOIN person ON p.phno = person.phno
LEFT JOIN person2 ON p.phno = person2.phno
LEFT JOIN person3 ON p.phno = person3.phno;

It does work, I just tested it myself, feel free to execute my test and confirm:

DROP TABLE IF EXISTS person;
CREATE TABLE person(NAME VARCHAR(255), phno INT);
DROP TABLE IF EXISTS person2;
CREATE TABLE person2(NAME VARCHAR(255), phno INT);
DROP TABLE IF EXISTS person3;
CREATE TABLE person3(NAME VARCHAR(255), phno INT);

INSERT INTO person(NAME, phno)
VALUES ('abc', 1234),('def', 2345),('ghi', 3456),('jkl', NULL);

INSERT INTO person2(NAME, phno)
VALUES ('abc', 1234),('def', 2345),('ghi', NULL),('jkl', 4567);

INSERT INTO person3(NAME, phno)
VALUES ('abc', NULL),('def', 2345),('ghi', 3456),('jkl', 4567);

SELECT DISTINCT p.phno AS 'phno',
(CASE WHEN ISNULL(person.phno) THEN 'NO' ELSE 'Yes' END) AS 'person',
(CASE WHEN ISNULL(person2.phno) THEN 'NO' ELSE 'Yes' END) AS 'person2',
(CASE WHEN ISNULL(person3.phno) THEN 'NO' ELSE 'Yes' END ) AS 'person3'
FROM (SELECT 4567 AS phno) p
LEFT JOIN person ON p.phno = person.phno
LEFT JOIN person2 ON p.phno = person2.phno
LEFT JOIN person3 ON p.phno = person3.phno;

result:

phno  person  person2  person3
4567  NO      Yes      Yes

Best Answer

Related Solutions

Mysql – Selecting minimum value using a subquery

MySQL getting result using 3 Tables and case statements

Related Question