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?
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;
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...
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.
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.
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;
Success!!! Thank you SO much! Not only does it work but it even kind of makes sense....
17 People are following this question.
