1 query from 2 table to know the last record by date ? (1 Viewer)

[Chris SL]

Hello

I have 2 tables

TableStartPrice and TableNewPrice
In each 3 same fields: Adress, Date, Price

In TableStartPrice only one record per adress
In TableNewPrice could be several records per adress

With a query I want to know the last price (by date) for each adress coming from TableStartPrice
I could be the price from TableStartPrice if no new price in TabeNewPrice or the last new price from TableNewPrice

In my query I join both tables by adress field with including all records from TableStartPrice

If I do like this (Attached QueryLasTPrice0)

SELECT TableStartPrice.adress_start, Max(TableNewPrice.date_new) AS MaxOfDate, TableStartPrice.date_start
FROM TableNewPrice RIGHT JOIN TableStartPrice ON TableNewPrice.[adresse_new] = TableStartPrice.[adress_start]
GROUP BY TableStartPrice.adress_start, TableStartPrice.date_start;

It is not too bad, I get only 1 record from TableNewPrice even for adresses has 2 records in TableNewPrice
But also get date from TableStartPrice even if there is a new price with new date in TableNewPrice.

First question how to get the only the last date record from those 2 tables ?


After I had price column in my query like this (Attached QueryLasTPrice1)

SELECT TableStartPrice.adress_start, Max(TableNewPrice.date_new) AS MaxOfDate, TableNewPrice.price_new, TableStartPrice.date_start
FROM TableNewPrice RIGHT JOIN TableStartPrice ON TableNewPrice.[adresse_new] = TableStartPrice.[adress_start]
GROUP BY TableStartPrice.adress_start, TableNewPrice.price_new, TableStartPrice.date_start;

But now I have 2 lines results for an adress has 2 records in the TableNewPrice (388 Denver)

How to solve that ?

Thanks by advance for your help
Chris

 

[arnelgp]

see the demo db, what you need is Query, Q2.

 

[gemma-the-husky]

Why have two tables. Surely you could do that more easily with a single table?

 

[Chris SL]

see the demo db, what you need is Query, Q2.

thank you for your answer but microsoft detects non trustable macro in your DB. Can you explain there your way to solve my issue ?

 

[Chris SL]

Why have two tables. Surely you could do that more easily with a single table?

sure, but I want to manage it with 2 tables

 

[ebs17]

but I want to manage it with 2 tables

What special reason is there for this, other than to use a maximized inconvenience that you also don't control?

 

[gemma-the-husky]

If you only have a single table, the start price is the price with the lowest date, and the current price is the price with the highest date. Far easier than multiple reads.

 

[Gasman]

thank you for your answer but microsoft detects non trustable macro in your DB. Can you explain there your way to solve my issue ?

Well trust it then?

FYI Q2 is
SELECT Q1.adresse_new, TableNewPrice.date_new, TableNewPrice.price_new
FROM Q1 INNER JOIN TableNewPrice ON (Q1.MaxOfdate_new = TableNewPrice.date_new) AND (Q1.adresse_new = TableNewPrice.adresse_new);

 

[arnelgp]

thank you for your answer but microsoft detects non trustable macro in your DB. Can you explain there your way to solve my issue ?

it will tagged as unwanted application because you download it. right click on the downloaded db and on it's property, Unblock it.

 

[Chris SL]

If you only have a single table, the start price is the price with the lowest date, and the current price is the price with the highest date. Far easier than multiple reads.

so maybe an union query could be the way ?

 

[Chris SL]

Well trust it then?

FYI Q2 is
SELECT Q1.adresse_new, TableNewPrice.date_new, TableNewPrice.price_new
FROM Q1 INNER JOIN TableNewPrice ON (Q1.MaxOfdate_new = TableNewPrice.date_new) AND (Q1.adresse_new = TableNewPrice.adresse_new);

Q1 is
SELECT TableNewPrice.adresse_new, Max(TableNewPrice.date_new) AS MaxOfdate_new
FROM TableNewPrice
GROUP BY TableNewPrice.adresse_new;

and Q2 is
SELECT Q1.adresse_new, TableNewPrice.date_new, TableNewPrice.price_new
FROM Q1 INNER JOIN TableNewPrice ON (Q1.MaxOfdate_new = TableNewPrice.date_new) AND (Q1.adresse_new = TableNewPrice.adresse_new);

Q1 and Q2 are related to TableNewPrice, but I have 2 tables and need to check last price from both table per each adress coming from first table which is TableStartPrice

Maybe something missunderstood ?

 

[cheekybuddha]

sure, but I want to manage it with 2 tables

You still didn't explain why two tables is better than one for you.

 

[Chris SL]

You still didn't explain why two tables is better than one for you.

on the first table I have more fields, all the informations about the property. in the second one only prices updates for the property

 

[cheekybuddha]

Ok, you said the tables had the same fields which we mistakenly understood to mean that the two tables were identical in structure

You should remove the prices from your first table.
All prices you in your related prices table.
Then the one with the oldest date is the original price, and the one with the MAX date is tree newest price.

 

[ebs17]

Possible solutions:

SELECT
   OT.*
FROM
   (
      SELECT
         Adress,
         MAX([Date]) AS MaxDate
      FROM
         OneTable
      GROUP BY
         Adress
   ) AS SQ
      INNER JOIN OneTable AS OT
      ON SQ.Adress = OT.Adress
         AND
      SQ.MaxDate = OT.[Date]

SELECT
   OT.*
FROM
   OneTable AS OT
WHERE
   OT.ID IN
      (
         SELECT TOP 1
            X.ID
         FROM
            OneTable AS X
         WHERE
            X.Adress = OT.Adress
         ORDER BY
            X.[Date] DESC
      )

OneTable is the merging of the two tables into one table, for example via UNION.

But you can save yourself UNION if you only have one table for the same content.

Problems with UNION:
1) Additional effort - costs performance
2) With a UNION query, index use is no longer possible - wasted performance
3) Ensuring a unique ID is problematic - solution 2 is omitted

 

[gemma-the-husky]

Having extra fields in the first price table sounds like a normalisation issue. I can't think of any fields that could be in that table only.

 

[Chris SL]

Ok, you said the tables had the same fields which we mistakenly understood to mean that the two tables were identical in structure

You should remove the prices from your first table.
All prices you in your related prices table.
Then the one with the oldest date is the original price, and the one with the MAX date is tree newest price.

the point was that I didnt want to insert datas in 2 tables for new properties

 

[Chris SL]

Possible solutions:

SELECT
   OT.*
FROM
   (
      SELECT
         Adress,
         MAX([Date]) AS MaxDate
      FROM
         OneTable
      GROUP BY
         Adress
   ) AS SQ
      INNER JOIN OneTable AS OT
      ON SQ.Adress = OT.Adress
         AND
      SQ.MaxDate = OT.[Date]

SELECT
   OT.*
FROM
   OneTable AS OT
WHERE
   OT.ID IN
      (
         SELECT TOP 1
            X.ID
         FROM
            OneTable AS X
         WHERE
            X.Adress = OT.Adress
         ORDER BY
            X.[Date] DESC
      )

OneTable is the merging of the two tables into one table, for example via UNION.

But you can save yourself UNION if you only have one table for the same content.

Problems with UNION:
1) Additional effort - costs performance
2) With a UNION query, index use is no longer possible - wasted performance
3) Ensuring a unique ID is problematic - solution 2 is omitted

thank you, seems that I need to do with one table

 

[arnelgp]

Q1 is
SELECT TableNewPrice.adresse_new, Max(TableNewPrice.date_new) AS MaxOfdate_new
FROM TableNewPrice
GROUP BY TableNewPrice.adresse_new;

and Q2 is
SELECT Q1.adresse_new, TableNewPrice.date_new, TableNewPrice.price_new
FROM Q1 INNER JOIN TableNewPrice ON (Q1.MaxOfdate_new = TableNewPrice.date_new) AND (Q1.adresse_new = TableNewPrice.adresse_new);

Q1 and Q2 are related to TableNewPrice, but I have 2 tables and need to check last price from both table per each adress coming from first table which is TableStartPrice

Maybe something missunderstood ?

Then you need a third query:

SELECT TableStartPrice.adress_start, TableStartPrice.date_start, Q2.date_new, Q2.price_new
FROM TableStartPrice LEFT JOIN Q2 ON TableStartPrice.adress_start = Q2.adresse_new;

 

[Chris SL]

Then you need a third query:

SELECT TableStartPrice.adress_start, TableStartPrice.date_start, Q2.date_new, Q2.price_new
FROM TableStartPrice LEFT JOIN Q2 ON TableStartPrice.adress_start = Q2.adresse_new;

thanks a lot for your help, I am thinking to save datas in one same table. according with all answers, I understand that it will be the best way for me