MIN(DATE) - SQL Server Forums

Please start any new threads on our new site at https://forums.sqlteam.com. We've got lots of great SQL Server experts to answer whatever question you can come up with.

All Forums

General SQL Server Forums

New to SQL Server Programming

MIN(DATE)

Author

Topic

brucezepplin
Starting Member

15 Posts

Posted - 2011-07-22 : 10:25:06

Hi, I have a database of people who have been admitted to hospital for a heart attack, but for people who have been admitted for heart attacks multiple times, I want to find the first date they were admitted for a heart attack. I have:


SELECT PERSON_ID, ADMIS_TYPE, ADMIS_DATE
FROM DATABASE
WHERE ADMIS TYPE = 'HEART_ATTACK'
AND ADMIS DATE = MIN(ADMIS_DATE)
GROUP BY PERSON_ID, ADMIS_TYPE, ADMIS_DATE

The error I am getting is:

Invaild use of aggregate function or OLAP function.

The ADMIS_DATE is in standard DATE format. So what's going on?

Thanks in advance!

Seventhnight
Master Smack Fu Yak Hacker

2878 Posts

Posted - 2011-07-22 : 10:28:55

I suspect this is what you want:

SELECT PERSON_ID, ADMIS_TYPE, ADMIS_DATE = Min(ADMIS_DATE)
FROM DATABASE
WHERE ADMIS TYPE = 'HEART_ATTACK'
GROUP BY PERSON_ID, ADMIS_TYPE, ADMIS_DATE

Plus... this is a MS SQL Server site, and I suspect you are on something else.

Corey

I Has Returned!!

webfred
Master Smack Fu Yak Hacker

8781 Posts

Posted - 2011-07-22 : 10:30:23

quote:
Originally posted by brucezepplin

Hi, I have a database of people who have been admitted to hospital for a heart attack, but for people who have been admitted for heart attacks multiple times, I want to find the first date they were admitted for a heart attack. I have:

SELECT PERSON_ID, ADMIS_TYPE, min(ADMIS_DATE) as ADMIS_DATE
FROM DATABASE
WHERE ADMIS TYPE = 'HEART_ATTACK'
GROUP BY PERSON_ID, ADMIS_TYPE

The error I am getting is:

Invaild use of aggregate function or OLAP function.

The ADMIS_DATE is in standard DATE format. So what's going on?

Thanks in advance!

Without ADMIS_DATE in group by!

No, you're never too old to Yak'n'Roll if you're too young to die.

brucezepplin
Starting Member

15 Posts

Posted - 2011-07-22 : 10:41:23

Webfred, that was exactly what I was looking for.

And Corey, I am using DB2, but find that the SQL Server forums are more supportive that the DB2. Since I know both I don't have any problems translating the answers on here back into DB2 SQL.

Seventhnight
Master Smack Fu Yak Hacker

2878 Posts

Posted - 2011-07-22 : 11:39:21

Doh... i botched that one

Corey

I Has Returned!!

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources