query of query

[cshow365]

My table consists of 3 columns:
1) DrugName
2) Dose
3) DateTaken

I want to be able to retrieve the most recent DateTaken for each different
DrugName.

For example:
DrugName Dose DateTaken
viagra 50 mg 01/14/2006
viagra 100 mg 02/04/2006
levitra 20 mg 11/05/2005
levitra 10 mg 12/16/2005

Running the query should yield ....
viagra 100mg 02/04/2006
levitra 10mg 12/16/2006

I having trouble writing the SQL (still a newbie). Any help would be
great!!!!!!!!

-Gary

[DogBot]

I really don't undertand your logic here but assuming it's a typo and you
really just want the results ordered by date in descending order:
Assume your table is called something like DosageMonitor

your SQL would look like this

SELECT *
FROM DosageMonitor
ORDER BY DateTaken DESC


However that would yield:
viagra 100 mg 2/4/2006
viagra 50 mg 1/14/2006
levitra 10 mg 12/16/2005
levitra 20 mg 11/5/2005

not
viagra 100mg 02/04/2006
levitra 10mg 12/16/2006
because the viagra doce was taken after the levitra one

[cshow365]

Actually, my post is accurate.
I only want one row returned per DrugName.

Thanks for taking a shot anyway.

-gary

[Dan Bracuk]

select drugname, max(datetaken)
from dosagemonitor
group by drugname

[cshow365]

That almost got me there!

I still need to retrieve the corresponding dose information though.
I tried:
SELECT DrugName, Dose, max(DateTaken) AS maxdate
FROM drugs
GROUP BY DrugName


but I get the following error:

Error Executing Database Query.
You tried to execute a query that does not include the specified expression
'Dose' as part of an aggregate function.

The error occurred in C:\CFusionMX7\wwwroot\Project
Alpha\Private\drugs\index.cfm: line 178

176 : max(DateTaken) as MaxDate
177 : FROM Drugs
178 : WHERE UserID = #SESSION.auth.UserID#
179 : GROUP BY DrugName
180 : </cfquery>

SQL SELECT DrugName, Dose, max(DateTaken) as MaxDate FROM Drugs WHERE
UserID = 1 GROUP BY DrugName
DATASOURCE drugs
VENDORERRORCODE -3011
SQLSTATE 42000

[Dan Bracuk]

Here is a reference on aggregating data.
[url]http://www.comp.nus.edu.sg/~ooibc/courses/sql/dml_query_aggr.htm[/url]

You have a syntax problem and a logic problem in your query. The syntax
problem is described in your error message. You have a field in your select
clause that is not in your group by clause.

The logic problem is that by including the dose in your select clause, you are
no longer going to get just one row per drug. You will get one row per
combination of drug and dose.

[reenaroy]

use this query

select drugname,dose,datetaken from test1 where DateTaken in (SELECT max(DateTaken) AS maxdate FROM test1 GROUP BY drugname)

[paross1]

Something like this with a correlated subquery?

SELECT d1.DrugName, d1.Dose, d1.DateTaken
FROM drugs AS d1
WHERE d1.DateTaken = (SELECT max(d2.DateTaken)
FROM drugs d2
WHERE d1.DrugName = d2.DrugName)

Phil

[cshow365]

That did it!
Don't worry, I will go through Chapter 30 of Forta's book and master this stuff.

Thank you!
-Gary