question

dillopk avatar image
dillopk asked

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?

querymaxsql services
10 |1200

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

MikeyBronowski avatar image
MikeyBronowski answered

What about this?


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;
10 |1200

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

dillopk avatar image
dillopk answered

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
10 |1200

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 ·
dillopk avatar image
dillopk answered

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
10 |1200

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 ·
dillopk avatar image
dillopk answered

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

10 |1200

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.