There is a table - "Tests", stores information about the devices under test:

CREATE TABLE Tests ( Test_id int NOT NULL, Test_Start_Date DATETIME NOT NULL, Device_Name VARCHAR(45) NOT NULL, );

In Device_Name (Number of the device under test) the values ​​are repeated (the device can be tested several times).

It is necessary to sort so as to leave only the unique device numbers with the maximum date.

Example:

enter image description here

The request should output:

enter image description here

I tried the query:

 SELECT Device_Name, MAX(Test_Start_Date) FROM Tests group by Device_Name

but he showed:

enter image description here

  • 2
    SELECT Device_Name, MAX (Test_Start_Date) as Test_Start_Date FROM Tests group by Device_Name Try this, if you're annoying "column name is missing" =) - Vladimir Klykov

1 answer 1

This happens when you do not name a column in which a function is used.

You can name a column by alias as , for example:

 SELECT Device_Name, MAX(Test_Start_Date) as last_test_start_date FROM Tests group by Device_Name

If you need the entire line with the last test, you can use this query:

 select t.* from tests t join (SELECT Device_Name, MAX(Test_Start_Date) as last_test_start_date FROM Tests group by Device_Name) m on t.device_name = m.device_name and t.test_start_date = m.last_test_start_date
  • Why in the second query join? would it not be better to simply list all the fields? or the difference in performance will not be at all? Why is communication not via id? - Vladimir Klykov
  • @Vladimir Klykov and how are you going to list all the fields if you do not group by id? Or I did not understand the course of your thoughts. The link is not through id, for the structure of the author’s table is not yet clear with a clear understanding that the more id is, the later the date. - Denis
  • I’m talking about this on t.device_name = m.device_name and t.test_start_date = m.last_test_start_date why there’s a link in two fields that may not be unique (and the author indicated it !!) - Vladimir Klykov
  • About all fields - your join with a subquery does exactly that instead of listing all the fields you can use t. * , In case you do not have to use it, you will need to list all the necessary fields by name. Test_id, Device_Name, MAX (Test_Start_Date) as Test_Start_Date will be the same as in your example, but without unnecessary join, if the author has more than 3 fields, you need to copy them all - Vladimir Klykov
  • one
    Everything, I understood where the fool is and for what purpose this join =) - Vladimir Klykov