November 18th, 2012, 02:23 AM
Join Date: Nov 2012
Time spent in forums: 26 m 43 sec
Reputation Power: 0
Query to pick out a row with lowest cost with latest date
I need to program a query that tries to pull the cheapest cost w/ date from a table of drugs
(has link_code, drugID, drugName, cost, dateOfPurchase .... yea it's a linear table for now).
Here's the twist: rows or drugs deemed to be similar will have the same link_code number. No link number just means
a similar drug hasn't been entered into the database.
So I will have another query with drugIDs that I need to combine with the results of this query:
If the drugID is found in the table then it'll see if there's a similar drug by checking out the link_code and
will look through all the rows grouped by that link_code to pick out the latest AND cheapest drug purchased.
Then the query will output drugID, drugname, cost, and dop(dateOfPurchase).
If the drugID is not found don't do anything.
I hope this is clear.