CODEWITHSUNDEEP
sqldatefirebird
Jasoomian
Jasoomian

Reputation: 179

Find most recent date, Firebird dBase

I have two tables containing data, and while I have seen many examples on how to do what I want with just one table (even here on SO), I can't quite figure out how to do it with more than one table using Firebird (which probably isn't that much more different than what most people would consider just plain SQL), but, I don't know enough about it to make a case.

Anyway -

I have the following bit of code:

select cqd.customer_price, cqd.entry_date, pnm.pn
  from cq_detail cqd, parts_master pnm
 where cqd.pnm_auto_key = pnm.pnm_auto_key
 order by cqd.entry_date desc

It returns the right data, as sampled below:

CUSTOMER_PRICE, ENTRY_DATE, PN
------------------------------
1,052.00        08.01.2012, 1938-12
1,054.00        08.02.2012, 1938-12
1,050.00        08.08.2012, 1938-12
1,051.00        08.04.2012, 1938-12
5,052.00        08.20.2012, 9999-19
7,054.00        08.07.2012, 9999-19
3,030.00        08.12.2012, 9999-19
6,021.00        08.04.2012, 9999-19

Which, I am perfectly happy with. Unfortunately, the powers that be would like the result set to be just:

CUSTOMER_PRICE, ENTRY_DATE, PN
------------------------------
1,050.00        08.08.2012, 1938-12
5,052.00        08.20.2012, 9999-19

SQL is not my normal bit of work, our normal SQL guy is out and I am trying to take care of some of the more simpler items while he's gone.

So, I guess, in a nutshell, I need to return whatever the most recent date is for each particular PN. I suspect I'll need a nested select or some form of JOIN, and those are a bit beyond me these days.

Thanks in advance.

JB

Upvotes: 2

Views: 724

Answers (1)

Anuj
Anuj

Reputation: 46

select a.pn,b.entry_date,c.pn from
(select  pnm.pn as pn,max(cqd.entry_date) as max_entry_date
  from cq_detail as cqd, parts_master pnm
 where cqd.pnm_auto_key = pnm.pnm_auto_key
group by pnm.pn) a

inner join 

(select cqd.customer_price as customer_price, cqd.entry_date as entry_date, pnm.pn as pn
  from cq_detail cqd, parts_master pnm
 where cqd.pnm_auto_key = pnm.pnm_auto_key) as b 
on a.pn=b.pn and a.max_entry_date=b.entry_date
order by b.entry_date

Upvotes: 3

Related Questions