I have a table:

  • number
  • employee
  • date (skip)

I need to find out in the request the numbers of the last pass for each employee, I write the request: select max(data),nomer from propuski group by nomer ... (works). And now I need to use this data in another request, just write type select ... where nomer in (этот_запрос) does not work, because This query returns 2 columns.

  1. How can I refer to exactly the nomer column
  2. And is it possible in the first query to display only a list of numbers? (i.e., I’m not interested in dates here, but I’ll need numbers for each employee)

    1 answer 1

    select max(data),nomer from propuski group by nomer

    With this request, you get lists of all unique numbers with a maximum date, and not for each employee. And if your nomer field is unique, then this query will be equivalent to the query select data, nomer from propuski

    In general, your problem is solved as follows:

     SELECT DISTINCT p.employer, p.nomer FROM propuski p JOIN (SELECT MAX(data) as max_data, employer FROM propuski GROUP BY employer) m ON p.data = m.max_data AND p.employer = m.employer

    In some DBMS, the query can be simplified. For example, in Postgres, you can use Window Functions for this.

    • Thanks, partially understood ... but in the join-e only the date and the employee ... but there is no number ... I tried to add, but with the grouping problem ... Another similar question hashcode.ru/questions/76315 With this, I understood .... - Rakzin Roman