CODEWITHSUNDEEP
sqlmysql
poke
poke

Reputation: 2982

SQL update query using aggregate function and group by in where clause?

I have a table that holds listing information for housing properties. A property may be in the table multiple times, once for every time it was listed. Here are the relevant columns: 

ListingID <- primary key
PropertyID
ListingEndDateTime

I am trying to develop a query to update the EndDateTime for the most recent listing for every property in the table. The query will set the EndDateTime to the same value for every property.

I've tried a few approaches have been so far unsuccessful. How do I write such a query?

Upvotes: 0

Views: 2467

Answers (3)

Mitch Wheat
Mitch Wheat

Reputation: 300559

Might need tweaking, but you get the general idea (SQL Server 2005 onwards):

WITH cteMostRecent (PropertyID, ListingEndDateTime, rownum) AS
(
   SELECT PropertyID, ListingEndDateTime,
   ROW_NUMBER() OVER (PARTITION BY PropertyID ORDER BY ListingEndDateTime DESC) as rownum
   FROM MyListingTable
)  

UPDATE cteMostRecent 
SET ListingEndDateTime = someDate
WHERE rownum = 1

Upvotes: 0

Jon Black
Jon Black

Reputation: 16559

the following assumes ListingID is an auto_incrementing primary key:

update PropertyListing p
inner join
(
select
 max(ListingID) as ListingID,
 PropertyID
from
 PropertyListing
group by
 PropertyID
) latest on latest.ListingID = p.ListingID
set
 p.ListingEndDateTime = now();

Upvotes: 2

RichardTheKiwi
RichardTheKiwi

Reputation: 107716

This allows multiple listings for the same property per date, the latest ListingID in such cases will be used. Otherwise, the latest date alone will identify the listing.

# create table PropertyListing(ListingEndDateTime Int, PropertyID Int, ListingID Int);

update PropertyListing L
inner join
(
select Max(B.ListingID) MaxListingID
FROM
(
select PropertyID, MAX(ListingEndDateTime) MaxListingEndDateTime
from PropertyListing
group by PropertyID
) A
inner join PropertyListing B
    on B.ListingEndDateTime = A.MaxListingEndDateTime and A.PropertyID = B.PropertyID
group by B.PropertyID, B.ListingEndDateTime
) C on C.MaxListingID = L.ListingID
set L.ListingEndDateTime = CURDATE() + 7;

I have used CURDATE() + 7 arbitrarily, set it to whatever date you need for all the records.

Upvotes: 0

Related Questions