# question

## Beginner's SQL Query / MAX function?

Raw beginners question - I have a table with just 3 fields - Address, PurchaseDate and PurchaseAmount. There are multiple rows for the same Address if it was purchased more than once (with a different amount for each date). What is the SQL statement for a query to find just the newest date for each Address and show all 3 fields related to that date?

Up to 2 attachments (including images) can be used with a maximum of 512.0 KiB each and 1.0 MiB total.

·

```CREATE TABLE #t (a varchar, d date, p int);
insert into #t VALUES
('a','2021-02-03',1),
('a','2021-02-02',10),
('a','2021-02-01',100),
('b','2021-02-03',200),
('b','2021-02-02',2),
('b','2021-02-01',20),
('c','2021-02-03',30),
('c','2021-02-02',300),
('c','2021-02-01',3);

WITH CTE (a,d) AS (
select a, max(d) from #t group by a
)
select t.* FROM CTE join #t t on cte.a = t.a and cte.d = t.d;
DROP TABLE #t;```

Up to 2 attachments (including images) can be used with a maximum of 512.0 KiB each and 1.0 MiB total.

Thank you, I sort of follow it (again my SQL skills are very primitive), but it looks like you are inserting values into a temporary table and then using it as the source for the selection statements. I already have a table, let's call it "Properties", with rows and rows of what you call a, d, and p. I don't want to hard-code my data values into the SQL code, I want to use the hundreds of rows already in my Properties table. Would I just need something like the SELECT and JOIN statements? Can this be accomplished without building a temporary table? Gotta keep it simple for me, pretend you are explaining it to your great-grandmother...

1 comment

Up to 2 attachments (including images) can be used with a maximum of 512.0 KiB each and 1.0 MiB total.

You are right, this is just an example, so you can run and see the results without affecting your table.
Try replacing #t with Properties in the query at the bottom and a=Address,d=PurchaseDate ,p=PurchaseAmount with your column names. Completely remove CREATE/INSERT/DROP from the code I posted.

0 Likes 0 ·

I apologize for my ignorance, not sure what to do with the WITH CTE / FROM CTE statements in your example. I think I just need a pretty simple SELECT with some kind of MAX and GROUP BY but I can't get the right syntax. I'm using Microsoft Access, the built-in wizards generate the SQL so (usually) I don't need to do any coding. In this case Access is not allowing me to pick the date as the field from which I am seeking the MAX value. Just trying to find the row with the highest date among rows with the same address and select all 3 fields from that row. Seems like a no-brainer if I knew what I was doing.

1 comment

Up to 2 attachments (including images) can be used with a maximum of 512.0 KiB each and 1.0 MiB total.

Oh, ok :) The query with CTE will work with SQL Server.

Try this for MS Access, in SQL View.

Inner query to get MAX date

`select Address, max(PurchaseDate ) AS LastestDate from Properties group by Address;`

And the final query

```select Properties.* FROM
(select Address, max(PurchaseDate ) AS LatestDate from Properties group by Address) AS Latest INNER JOIN Properties on Latest.Address = Properties.Address and Latest.LatestDate = Properties.PurchaseDate;```
0 Likes 0 ·