Select using a Min Function 1

[maxf]

I have a select statement where I need to select data from 2 columns. From one of these columns, I also need to use the Min function to select the Min number from the data. Heres what my statement looks like:

mySQL1="SELECT h.year, h.MDOfficeVisit AS [data], MIN(h.MDOfficeVisit) AS [MinData] FROM historical h WHERE h.condition_id=" & intcid & " AND h.factdataregion_id=" & intdsource & " Order By h.year ASC"


I am getting an error that this statement cannot be performed. How can I accomplish this?

thanks

 

[mwolf00]

I'm a little confused by the question... It seems to me that since you want all data for a certain condition, you can use an "order by" to give you the minimum (ie - the first record returned). The current query won't work because you are using an aggregate function (MIN) without using a GROUP BY for all data that is not included in the aggregate.

If you really wanted a third column returned, you could join to the table again to get the MIN column...

 

[maxf]

Im using the order by clause to give me the first value for the h.year column, but I also need to find the smallest value in the h.mdofficevisit column for all the records which meet my condition. So I want to select all the values for the years (h.year) and values (h.mdofficevisit as Data) which meet my conditions, then from these records, I want to retrieve the smallest value from the (h.mdofficevisit as Data) column.

thanks for your help

 

[sunila7]

hi,

does this query return what u r looking for


SELECT h.year, h.MDOfficeVisit AS [data],
(SELECT MIN(h1.MDOfficeVisit) FROM historical h1 Where h.condition_id=h1.condition_id
AND h.factdataregion_id = h1.factdataregion_id) as mindata
FROM historical h
WHERE h.condition_id=1
AND h.factdataregion_id= 2
Order By h.year ASC

sunil

 

[maxf]

That did it!! Thanks. One more question. If I wanted to add a Max Function to select the Max year from the h.year column would I do this:

SELECT h.year, h.MDOfficeVisit AS [data],
(SELECT MIN(h1.MDOfficeVisit) FROM historical h1 Where h.condition_id=h1.condition_id
AND h.factdataregion_id = h1.factdataregion_id) as mindata,
(SELECT MAX(h2.year) FROM historical h2 Where h.condition_id=h2.condition_id
AND h.factdataregion_id = h2.factdataregion_id) as MAXYEAR
FROM historical h
WHERE h.condition_id=1
AND h.factdataregion_id= 2
Order By h.year ASC

 

[sunila7]

Hi,

Yes that should work....


sunil