Group by Query----to also include respective record details

Thread view:

Enable EMail Alerts

Start New Thread

Thread rating:

MFR - 14 Sep 2006 13:06 GMT

In my query I would like to see the Security Number (I used Group by
this)----Last Trade Time (I used "max" for this one) -----but I also want it
to include the respecitve information for the last trade----Volume and Price.
I've managed to isolate the the Security Number so each is unique, and
identified the Last trade of the day, but how can I get it to also show the
other two fields----for that trade?

MFR

Reply to this Message

Gary Walter - 14 Sep 2006 14:41 GMT

> In my query I would like to see the Security Number (I used Group by
> this)----Last Trade Time (I used "max" for this one) -----but I also want
[quoted text clipped - 5 lines]
> the
> other two fields----for that trade?

one way:
(replace "yurtable" w/ name of your table)

SELECT
[Security Number],
[Last Trade Time],
Volume,
Price
FROM
yurtable
WHERE
[Last Trade Time] =
(SELECT
Max(t.[Last Trade Time])
FROM
yurtable As t
WHERE
t.[Security Number] = yurtable.[Security Number]);

the advantage of putting the aggregation
in the WHERE clause should get you an
updateable query....

Reply to this Message