How to get the max date from a specific foreign key from SQL Server?-CodePudding

I am creating an application in NodeJs. In this I have a SQL Server database in which I save data and perform queries.

One of these queries in particular doesn't work for me, I think it's due to my inexperience with SQL.

The step table stores the steps performed by a subject throughout history, so it can have one or more steps (one or more rows in the table with the same contact_id but different dates->(FECHA)).

I am using this query, but it returns empty.

SELECT FECHA, id_contacto FROM step WHERE id_contacto = 680 
AND FECHA = (SELECT MAX(FECHA) FROM step).

What I want to obtain is the last step of the subject 680, that is, for all the possible steps of the contact 680, obtain the one where DATE is the largest.

this is the structure of the table.

CodePudding user response：

Lets assume we have table with two records id_contacto=1 FECHA='2022-09-30' id_contacto=680 FECHA='2021-10-31'

Your subquery

 SELECT MAX(FECHA) FROM step

returns '2022-09-30' (maximum date in the table) then your query is

SELECT FECHA, id_contacto FROM step WHERE id_contacto = 680 
 AND FECHA ='2022-09-30'

table does not contain the required data, so resultset is empty