CODEWITHSUNDEEP
sql-server
Vladyslav Ovcharenko
Vladyslav Ovcharenko

Reputation: 115

How to get only last row after inner join?

I need to return unique funeral_homes who contains not completed leads and sort these by last lead timestamp.

This is my sql query:

select distinct h.funeral_home_name, h.funeral_home_guid, h.address1, h.city, h.state, p.discount_available, t.date_created
from tblFuneralHomes h inner join tblFuneralTransactions t on h.funeral_home_guid = t.funeral_home_guid 
inner join vwFuneralHomePricing p on h.funeral_home_guid = p.funeral_home_guid where completed=0 order by 'funeral_home_name' asc;

This is the result, but I need only unique homes with last added lead

enter image description here What I should change here?

Upvotes: 1

Views: 435

Answers (1)

StuartLC
StuartLC

Reputation: 107357

The problem here appears that you are joining into tables with 1 to many relationships with table tblFuneralHomes, yet you expect only one row per funeral home.

Instead of using distinct, I would suggest that instead you group by the required output funeral home columns, and then apply some kind of aggregate on the columns needed from the joined tables in order to return just a single computed value from all possible joined values.

For instance, below we find the first transaction date (min) associated with each funeral home:

select h.funeral_home_name, h.funeral_home_guid, h.address1, h.city, h.state,
       p.discount_available, min(t.date_created)
from tblFuneralHomes h 
inner join tblFuneralTransactions t on h.funeral_home_guid = t.funeral_home_guid 
inner join vwFuneralHomePricing p on h.funeral_home_guid = p.funeral_home_guid 
where completed=0 
group by h.funeral_home_name, h.funeral_home_guid, h.address1, h.city, h.state, 
         p.discount_available
order by h.funeral_home_name asc

Note that depending on the cardinality of the association between tblFuneralHomes and vwFuneralHomePricing, you may also need to remove p.discount_available from the grouping and also introduce it with an aggregate function, similar to what I've done with t.date_created

Upvotes: 1

Related Questions