2. PHPRunner General questions
  3. Simple SQL problem?
This topic is locked

Simple SQL problem?

 		1/5/2011 04:39:06
PHPRunner General questions
T
 tparki authorDevClub member

I have a table with multiple price and date entries for each reference and I need to establish the price against the latest date.

The SQL below yields latest date but the first price for the reference not the one associated with latest date.

SELECT

Ref,

MAX(Date) AS MAX(Date),

Price

FROM prices

GROUP BY Ref

ORDER BY Ref, Date

I have tried an INNER JOIN on Ref and Date with the prices table which worked in ACCESS but got the same result.

Grateful for any tips

Tim
P
 		procheck 1/5/2011

Hi Tim,
I think that you need to setup a subselect - something like this.
SELECT

Ref,

'Date`,

Price

FROM prices A

where A.Date =

(SELECT MAX(B.Date)

FROM prices B )

Order...

Group...
The above will probably need some tweeking but I think that is the general idea.
Al
P.S. - There shouldn't be a space between the B and ) in the subselect but with no space it

is being intrepreted as a happy face.
T
 		tparki authorDevClub member 1/6/2011

Thanks for responding, Al

Have tried your suggestion with

SELECT

prices.Ref,

prices.Price,

prices.Date,

prices1.Date AS Date1

FROM prices

where prices.Date =

(SELECT MAX(Date1)

FROM prices1)

INNER JOIN prices AS prices1 ON prices.Ref = prices1.Ref AND prices.Date = prices1.Date

GROUP BY prices.Ref

ORDER BY prices.Ref

but MySQL SQL reports a problem with the INNER JOIN syntax after adding the SUB SELECT bit
My problem comes about because you can't relate Views in phpRunner as you can queries in ACCESS
P
 		procheck 1/6/2011

This is a bit more difficult than your original question

Try getting it to work first without the extra code like INNER JOIN, Group & Order.
I think you need:
(SELECT MAX(B.Date)

FROM prices1 B ) <<< again no space because of the happy face.
I'm not 100% sure on this one, so don't be afraid to experiment.

It might be worthwhile to read up on sub-selects because they can be tricky.