0

background: Using OLEDB driver to connect to a VFP database. Scripting: php

below is a sample set of data item purchases:

partno  purch_date  price
  A     04/02/2012    95
  A     04/01/2012   100
  B     02/28/2012    55
  B     03/15/2012    60

what i want to do is to build a select query that would get the prices of the latest purchase price based on the date. this sounds simple enough but i could not for the life of me figure out how to do this. it seems like there must be an aggregate function that i dont know about. im thinking it should be something like the following:

SELECT partno, max(purch_date) as lastest_purch_date, price FROM table GROUP BY partno

this query will not work because VFP will require an aggregate function for all fields selected. what im missing is a function or someway to tell vfp that i want the price corresponding to max(purch_date)

any help will be greatly appreciated. thanks.

AKINGA
  • 1
  • 1

1 Answers1

2

Try this

select b.partno,b.purdate,c.price
from
(
select max(a.purdate) purdate,partno from table a
group by a.partno
)b,
table c
where b.partno=c.partno and b.purdate=c.purdate
Deb
  • 981
  • 13
  • 39
  • i guess this will work. i was trying to avoid having a whole messy query because my query actually involves joins and subqueries already. anyways, thanks for the input. – AKINGA Apr 19 '12 at 14:50
  • Whats so messy about this ? i would be glad to know – Deb Apr 19 '12 at 17:16
  • aside from visually appealing sql, I don't see anything messy about the query. They wanted the most recent purchase date per part... you need that "prequery" to get the answer anyhow.. – DRapp May 14 '12 at 10:45