SELECT ROW based the newest date

[sal21]

Attached is query to estract value based from IDCLI.
But i need to to extract also by the newest date in field IL.
in my case the date is 27/12/2024

plese with SQL for vb 6.0 and ado

[HansV]

Dim SQL As String
SQL = "SELECT IDCLI, COGNOME, NOME, DTNASC, NATOA, INDIRIZZO, PR, CAP, COM_RES, TELEFONO, CITTADIN, EMAIL FROM ANAGRAFICA WHERE IDCLI=61 AND IL=(SELECT Max(IL) FROM ANAGRAFICA WHERE IDCLI=61)"

[sal21]

Dim SQL As String
SQL = "SELECT IDCLI, COGNOME, NOME, DTNASC, NATOA, INDIRIZZO, PR, CAP, COM_RES, TELEFONO, CITTADIN, EMAIL FROM ANAGRAFICA WHERE IDCLI=61 AND IL=(SELECT Max(IL) FROM ANAGRAFICA WHERE IDCLI=61)"

TKS.
Work.
But if i delete the IDCLI from the where clausole the SQL return only two recordset!
I need all row, always max(IL), im my case 9 rows

[HansV]

Sorry, I don't understand

[sal21]

Sorry, I don't understand

in effect extract all with max date in IL.
Naturally if a recorset have only one date in IL the max date of IL is the unique date.

[HansV]

SQL = "SELECT IDCLI, COGNOME, NOME, DTNASC, NATOA, INDIRIZZO, PR, CAP, COM_RES, TELEFONO, CITTADIN, EMAIL FROM ANAGRAFICA WHERE IL=(SELECT Max(T.IL) FROM ANAGRAFICA AS T WHERE T.IDCLI=ANAGRAFICA.IDCLI)"

[sal21]

SQL = "SELECT IDCLI, COGNOME, NOME, DTNASC, NATOA, INDIRIZZO, PR, CAP, COM_RES, TELEFONO, CITTADIN, EMAIL FROM ANAGRAFICA WHERE IL=(SELECT Max(T.IL) FROM ANAGRAFICA AS T WHERE T.IDCLI=ANAGRAFICA.IDCLI)"

GREAT!

[sal21]

SQL = "SELECT IDCLI, COGNOME, NOME, DTNASC, NATOA, INDIRIZZO, PR, CAP, COM_RES, TELEFONO, CITTADIN, EMAIL FROM ANAGRAFICA WHERE IL=(SELECT Max(T.IL) FROM ANAGRAFICA AS T WHERE T.IDCLI=ANAGRAFICA.IDCLI)"

Sorry bro but in my new query have a JOIN, can you mofify the query?
Tks

Code: Select all

SELECT [ANAGRAFICA].[IDCLI] & '-' & [PAGAMENTI].[COGNOME] & ' ' & [PAGAMENTI].[NOME] & '-(' & [ANAGRAFICA].[DTNASC] & ')' AS NOMINATIVO, ANAGRAFICA.INDIRIZZO, ANAGRAFICA.PR, ANAGRAFICA.CAP, ANAGRAFICA.COM_RES, [ANAGRAFICA].[IDALL] & '-' & [ANAGRAFICA].[TIPO_ALL] AS ALLENAMENTO, ANAGRAFICA.PERIODO, ANAGRAFICA.IL, ANAGRAFICA.QUOTA
FROM PAGAMENTI INNER JOIN ANAGRAFICA ON PAGAMENTI.IDCLI=ANAGRAFICA.IDCLI
WHERE ANAGRAFICA.IDCLI=68 AND PAGAMENTI.IDNR=20

[HansV]

Please attach a sample database.

[sal21]

Please attach a sample database.

here the database

[HansV]

There is only 1 value of IL in both tables...