how to sort on 1 column and only display max date most recent record from another column.

[treichelt]

I use access 2021 as a front end with mariadb server, this query gives me the exact result which shows me only the drive letter with the most recent date (row_create_ts)
we use this table for historical data about drive size changes.
SELECT cm_server_drives.drive_letter,cm_server_drives.dri ve_size, Max(cm_server_drives.row_create_ts),cm_sizes.size


FROM cm_server_drives,cm_servers,cm_sizes
WHERE cm_server_drives.server_name = 'hqabqditdcsmtsr'
AND cm_server_drives.size_id = cm_sizes.size_id
GROUP BY cm_server_drives.server_name,drive_letter;

I pasted that into the sql window in access but of course get a bunch of errors.
all the info drive_letter, drive_size, drive_size_id, row_create_ts are in one table. (cm_server_drives)
the table shows different entries for each drive letter, what size it was changed to and the date it was changed. (row_create_ts).
What i want the query to display is the drive letter, size of the row that has the most recent date (row_create_ts) for each drive letter.

I just can't figure out how to do this in access. any help how to do this would be greatly appreciated.

[madpiet]

That SQL statement is ummm interesting.

I think I remember syntax like that from maybe 1999? I seriously haven't seen joins done in a where clause since forever ago.
I was going to say you normally use a TOP clause and either a correlated subquery or use CROSS APPLY. But heaven knows how you do that in MariaDB. Allen Browne, who wrote one of the first serious books on Access, has an example of using TOP in a subquery on his website. www.allenbrowne.com

[CJ_London]

You are not grouping on all the fields selected and grouping on fields not selected

[treichelt]

That SQL statement is ummm interesting.

I think I remember syntax like that from maybe 1999? I seriously haven't seen joins done in a where clause since forever ago.
I was going to say you normally use a TOP clause and either a correlated subquery or use CROSS APPLY. But heaven knows how you do that in MariaDB. Allen Browne, who wrote one of the first serious books on Access, has an example of using TOP in a subquery on his website. www.allenbrowne.com

don't know about 1999 but sure works well in mysql. i use heidisql to connect to the db (odbc) and enter that query and it produces exactly what i needed. the last enter record (max date for row_create_ts) for each drive.
so only the latest for the c: d: etc.

Since access is different I was hoping someone would understand the query and poit me inot the right direction how to do that in access since it is different

[treichelt]

here is the ms access sql statement it will show me every record for every drive letter. I cannot figure out how to only get the record for each drive letter based on the the most recent date (row_create_ts)
SELECT cm_server_drives.drive_letter AS Drive, cm_server_drives.drive_size AS [Size], cm_server_drives.size_id AS Unit, cm_server_drives.row_create_ts AS [Created on], cm_server_drives.row_create_by AS [Created by], cm_server_drives.server_name
FROM cm_server_drives INNER JOIN cm_sizes ON cm_server_drives.size_id = cm_sizes.size_id
WHERE (((cm_server_drives.server_name)=[Forms]![frm_servers]![Server]))
ORDER BY cm_server_drives.drive_letter, cm_server_drives.row_create_ts DESC;

[CJ_London]

you need an aliased query to limit the record to the last date

SELECT cm_server_drives.drive_letter AS Drive, cm_server_drives.drive_size AS [Size], cm_server_drives.size_id AS Unit, cm_server_drives.row_create_ts AS [Created on], cm_server_drives.row_create_by AS [Created by], cm_server_drives.server_name
FROM (cm_server_drives INNER JOIN cm_sizes ON cm_server_drives.size_id = cm_sizes.size_id) INNER JOIN (SELECT drive_letter, Max(row_create_ts) AS MaxDate FROM cm_server_drives GROUP BY drive_letter) LD ON cm_server_drives.drive_letter=LD.drive_letter AND cm_server_drives.row_create_ts=LD.MaxDate
WHERE (((cm_server_drives.server_name)=[Forms]![frm_servers]![Server]))
ORDER BY cm_server_drives.drive_letter, cm_server_drives.row_create_ts DESC

[treichelt]

you need an aliased query to limit the record to the last date

SELECT cm_server_drives.drive_letter AS Drive, cm_server_drives.drive_size AS [Size], cm_server_drives.size_id AS Unit, cm_server_drives.row_create_ts AS [Created on], cm_server_drives.row_create_by AS [Created by], cm_server_drives.server_name
FROM (cm_server_drives INNER JOIN cm_sizes ON cm_server_drives.size_id = cm_sizes.size_id) INNER JOIN (SELECT drive_letter, Max(row_create_ts) AS MaxDate FROM cm_server_drives GROUP BY drive_letter) LD ON cm_server_drives.drive_letter=LD.drive_letter AND cm_server_drives.row_create_ts=LD.MaxDate
WHERE (((cm_server_drives.server_name)=[Forms]![frm_servers]![Server]))
ORDER BY cm_server_drives.drive_letter, cm_server_drives.row_create_ts DESC

thank you that worked perfectly and makes more sense now with the inner join.

Thank you

[treichelt]

Hi, just a follow up question. I thought the query quit working but come to find out that all of the sudden, not aware of any changes, for some servers, it does not show all the current drives.
What I did discover is that for one of the servers that had multiple drives, i.e. C, D, E and F, only the E: with the most current date for that E: drive shows, none of the other drives. For other servers in the table it does show all drives with only the most current date but there are other servers that have the same issue as well, so not just that one server.
When i added a new drive for the server that does not show all the drives, that new drive now shows, so now it shows E and the new drive letter. I did some maintenance and checking on the tables, no problems found.
I'm at a loss why it would not return all the records for only some of these servers. Do you have any suggestions what I can do to find a solution for this?
Thank you

you need an aliased query to limit the record to the last date

SELECT cm_server_drives.drive_letter AS Drive, cm_server_drives.drive_size AS [Size], cm_server_drives.size_id AS Unit, cm_server_drives.row_create_ts AS [Created on], cm_server_drives.row_create_by AS [Created by], cm_server_drives.server_name
FROM (cm_server_drives INNER JOIN cm_sizes ON cm_server_drives.size_id = cm_sizes.size_id) INNER JOIN (SELECT drive_letter, Max(row_create_ts) AS MaxDate FROM cm_server_drives GROUP BY drive_letter) LD ON cm_server_drives.drive_letter=LD.drive_letter AND cm_server_drives.row_create_ts=LD.MaxDate
WHERE (((cm_server_drives.server_name)=[Forms]![frm_servers]![Server]))
ORDER BY cm_server_drives.drive_letter, cm_server_drives.row_create_ts DESC

[CJ_London]

Do you have any suggestions what I can do to find a solution for this?

Not really - how is the table being populated? What event causes it to be either repopulated or updated?

for example this comment

When i added a new drive for the server that does not show all the drives, that new drive now shows,

implies adding a new drive was an event that updated the table

You could also look at the MariaDb documentation