Query to pick out a row with lowest cost with latest date

[mcchung52]

Hi all,
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.
Thanks.

[June7]

Need to work with data. Want to provide db for analysis? Follow instructions at bottom of my post.

[mcchung52]

here is the attachment.
i only have 5 products as a sample.
sorry for getting back so late.

[June7]

Is the field [code] the link_code?

There is only one table in this db. Your requirement to determine 'If the drugID is found in the table' doesn't make sense unless the intent is to prompt user for a drugID (ndc ?) and search table for this parameter and return lowest cost.

However, consider this query:
SELECT ID, ndc, [drug name], [item#], [date], cost FROM gen WHERE gen.ID IN (SELECT TOP 1 ID FROM gen As Dupe WHERE Dupe.ndc=gen.ndc ORDER BY Dupe.ndc, Dupe.[date] DESC, Dupe.cost);

BTW, advise not to use spaces, special characters, punctuation (underscore is exception) in names, nor reserved words (such as 'date') as names.

[mcchung52]

yes it is the link code.
and i know it doesn't make sense because i'm gonna join this query with another query that has the same drugID.
all advices taken and greatly appreciated.
i'll try the query...
thanks.

EDIT: Wow that query takes mighty long time.

Is the field [code] the link_code?

There is only one table in this db. Your requirement to determine 'If the drugID is found in the table' doesn't make sense unless the intent is to prompt user for a drugID (ndc ?) and search table for this parameter and return lowest cost.

However, consider this query:
SELECT ID, ndc, [drug name], [item#], [date], cost FROM gen WHERE gen.ID IN (SELECT TOP 1 ID FROM gen As Dupe WHERE Dupe.ndc=gen.ndc ORDER BY Dupe.ndc, Dupe.[date] DESC, Dupe.cost);

BTW, advise not to use spaces, special characters, punctuation (underscore is exception) in names, nor reserved words (such as 'date') as names.

[mcchung52]

so what does that query do? exactly?

[June7]

It returns the lowest cost for each drug for the latest date associated with each drug in the table. Isn't that what you wanted?

Now you can build a form where user enters criteria for drug to search for if that is what you were intending by 'If the drugID is found in the table'.

[mcchung52]

oh wow ok. thank you. let me play with this and get back to you.