Re: SQL Query help needed
I haven't gotten the original post yet but:
EFergu@pagenet.com (Eva Ferguson) wrote:
:The following select will work if you don't mind getting the max(value)
:returned as well as hour.
:select max(value),hour from table_name group by name,date
This is a non functional sql statement in Informix. You have to group
by every column that's not in a function in the select list (hour in
this case). You can't group by hour, as that is the column we want
returned.
You can also use neither min(hour) nor max(hour) as any of those may
be the right one, and there is no way to know in advance.
I think the following will do it but these selects are allways hard
and I have not realy thought it through 100%:
select hour
from table_name tb1
where value in
(select max(value)
from table_name tb2
where tb2.name = tb1.name
and tb2.date = tb1.date)
If you have two rows with the same value for the same date and name
and this is the largest value, you will get both hour-values. You may
or may not want that.
There may be other problems.
If you want other columns than just hour returned you can of course
simply add them to the select list.
:Hope this helps,
:Eva Ferguson
:}----------
:}From: Rob Allen[SMTP:slcc@cnwl.igs.net]
:}Sent: Wednesday, November 13, 1996 3:12 PM
:}To: informix-list@rmy.emory.edu
:}Subject: SQL Query help needed
:}
:}I am trying to figure out the query to select this type of information.
:}
:}-------------------------------
:}| name | date | hour | value |
:}-------------------------------
:}| a|961010| 0100| 32|
:}| b|961012| 1600| 3423|
:}| a|961010| 0200| 345|
:}| a|961011| 0300| 0|
:}| a|961010| 1700| 999|
:}
:}and so on.
:}
:}What I am looking for is the hour that is related to the max(value) for
:}grouped by the name and date.
:}
:}In the above I would hope to get
:}1600 for b-961012-1600-3423
:}1700 for a-961010-1700-999
:}0300 for a-961011-0300-0
:}
:}I thank you in advance for help you can provide.
:}
:}Please reply to slcc@cnwl.igs.net
:}
Nils.Myklebust@idg.no
NM Data AS, P.O.Box 9090 Gronland, N-0133 Oslo, Norway
My opinions are those of my company
The Informix FAQ is at http://www.iiug.org