-1

I am running an sql query (using microsoft report builder)joining three tables and getting duplicates, i cant seem to stop this.

i have one table (tableA) that holds the relevant fields ownerID, propertyID, vacationDate. I want to find the name of the owner and the latest property they were in. (they may have been in several properties, i am only interested in the latest one) The Owners name is held on the Ownertable and linked by ownerID and the property address is found under the propertytable and linked by propertyID. I was trying to get each OwnerID with the latest vacation date and that particular propertyID then inner join the two tables to get the name and address. I have tried selecting 'max(vacation)' and then GROUP BY, but I am having problems because i am using report builder and have to use the group by clause on all fields not just the OwnerID, this brings up the latest vac date for each individual property aswell. Please can some one set me on the right track. Many thanks.

a sample of what i have done;

sorry im not on my pc to be give an exact sample here is a simplified version if this helps.

table A

ownerid      propertyid      vacation
1            2               1991
2            5               1993
1            3               1992
1            4               1997
2            6               2000
2            9               2007

so i want to select one row for each owner with the property id of the last vacation, for this example i would want this as below

ownerid     propertyid    vacation
1           4             1997
2           9             2007

I tried this below but get duplicates.

SELECT tableA.propertyid, tableA.ownerid, MAX(tableA.vacation), propertytable.propertyname, ownertable.ownername FROM tableA 

INNER JOIN propertytable on tableA.propertyid = propertytable.id

INNER JOIN Ownertable on tableA.ownerid = ownertable.id

GROUP BY tableA.property, tableA.owner, propertytable.propertyname, ownertable.ownername
Larry
  • 3
  • 2
  • And without sample data we have no way of knowing what your data looks like. – Gordon Linoff Feb 11 '18 at 17:10
  • I guess, in order to remove the duplicates you need to mention all the columns that you have used in the select in the group by as well. try giving it a shot!! – Jayanth Feb 11 '18 at 17:50
  • hi Gordon i have edited my answer trying to give a sample for you. Hi Jayanth i believe that's what i have done as it makes me group by all the selected fields, this however seems to then give every property not just the ones with the latest vacation date – Larry Feb 11 '18 at 20:36

1 Answers1

0

I think you have to write a sub query to

" to get each OwnerID with the latest vacation date" as bellow 

    SELECT
rs.ownerid,
rs.propertyid,
rs.vacation
FROM
(
SELECT
ownerid,
propertyid,
vacation,
MAX(vacation) OVER(PARTITION BY ownerid) AS maxDate
FROM  TableA

 ) as rs  

  WHERE rs.vacation = rs.maxDate
Prabhath Amaradasa
  • 493
  • 2
  • 7
  • Hi Prabhath, Thanks. Unfortunately I have tried similar to this and it still bring up duplicates. I may have mislead you by not giving you a sample of the database , i have tried to provide an example now in my question now. The ownerID can appear several times in TableA under different properties, i only want the one with the latest vacation date. If i get those ownerID's in the subquery, when i do a join on the main table by OwnerID it just brings all the properties that are linked to that ownerID, not just the latest vacation. unless i am doing something wrong? any ideas? many thanks – Larry Feb 11 '18 at 21:03
  • Ok. I have Sql for that issue in Fiddler link (http://sqlfiddle.com/#!18/3257c/11) . – Prabhath Amaradasa Feb 11 '18 at 21:38
  • That is exactly what i was after. thank you very much. – Larry Feb 11 '18 at 23:08