Re: SQL Query help needed
Rob Allen wrote:
>
> 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
Try:
select t1.hour
from tbl t1
where t1.hour = (
select max(hour)
from tbl
where name = t1.name
and date = t1.date
)
I am not near any of my machines with either Ingres or Access on it
to try this, so I havn't tested it. I have used this sort of query
before in Ingres without any troubles, and I remember this sort of
query from my uni days, so the idea should work in SQL-92 systems.
--
Brett Bailie Ingres DBA Sydney
bailieb@hutch.com.au NSW Supply NSW 2010
+61 2 339 7841 Dept Public Works Australia
Any opinions or ignorances expressed are unlikely those of my employer!