# 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 ·

Success!!! Thank you SO much! Not only does it work but it even kind of makes sense....

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

### Write an Answer

Hint: Notify or tag a user in this post by typing @username.

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