I have a recipients and recipient_history table. In the recipients table there are such fields as:

  • RECIPIENT_ID,
  • RECIPIENT_NAME

and in the recipient_history table:

  • RECIPIENT_ID,
  • START_DATE,
  • END_DATE
  • JOB_NAME,
  • DEPARTAMENT_ID.

I need to display the name and last place of the employee robots. It should be borne in mind that in the recipient_history table, the same employee may meet twice with different JOB_NAMEs.

for example

RECIPIENT_ID[1|1|2], START_DATE[12.03.2012|12.03.2015|15.04.2012], END_DATE[12.03.2014|12.03.2016|12.03.2013], JOB_NAME[it_spec,it_prog, manager], DEPARTAMENT_ID[101,102,103]

  • select max (END_DATE) group by RECIPIENT_ID and append ecipient_history again by these two fields. - nick_n_a
  • I did something like this but I can't do it - user223384
  • You can on more - user223384
  • And what SQL? sql-server has a nice version with window functions for example. In MySQL, it is solved by other methods, for example, how here ru.stackoverflow.com/questions/412928 And a bunch of such answers here, by the way, ru.stackoverflow.com/… - Mike

4 answers 4

Unfortunately, you cannot get by without nested queries.

 SELECT R.RECIPIENT_NAME AS RECIPIENT_NAME, IFNULL(RH2.JOB_NAME,'') AS JOB_NAME FROM recipients R LEFT JOIN ( SELECT RECIPIENT_ID, MAX(END_DATE) AS END_DATE FROM recipient_history RH GROUP BY RECIPIENT_ID ) RH ON RH.RECIPIENT_ID = R.RECIPIENT_ID LEFT JOIN recipient_history RH2 ON RH.END_DATE = RH2.END_DATE AND RH.RECIPIENT_ID = RH2.RECIPIENT_ID ORDER BY RH.END_DATE DESC;

Nested queries are evil, they generate temporary tables when processing a query.

    You need

    1. In the recipient_history table, find the records with the maximum date.
    2. The result of this sample is combined with the recipient .

    Point (2) is not difficult. How to find (1)? There are several ways.

    • One of them suggested @ArtemAleksashkin: group, find the maximum date, and by this date combine with the same table to get the entire row.

    • A dirty trick using the "feature" of MySQL: with default settings, it allows grouping with the output of all fields, not just the grouped + aggregates. It is only necessary to ensure the order we need (decision of the fu, I think):

      SELECT * FROM
      (
      SELECT * FROM recipient_history
      ORDER BY END_DATE DESC
      ) t1
      GROUP BY RECIPIENT_ID

    • Open self-union with comparison over / under and cut-off by NULL:

      SELECT o. *
      FROM recipient_history o
      LEFT JOIN recipient_history b
      ON o. RECIPIENT_ID = b. RECIPIENT_ID
      AND o. END_DATE <b. END_DATE
      WHERE b. END_DATE is NULL

      Make a simple request, just add at the end

       ORDER BY `END_DATE` DESC LIMIT 0,1
      • "Normal request" is what? - andreymal

      SELECT r.RECIPIENT_NAME, h.START_DATE, h.END_DATE, h.JOB_NAME FROM recipients r, recipient_history h WHERE r.RECIPIENT_ID = h.RECIPIENT_ID AND h.START_DATE = (SELECT MAX(h2.START_DATE) FROM recipient_history h2 WHERE r.RECIPIENT_ID = h2.RECIPIENT_ID)