The first() function

[Not Me]

Hi,

When using the first() function, either on a table directly or on a ordered
table, the results are the same.

Why is this? I thought it could be used on ordered results to pick the first
record of many groups?

Please help,
Chris

 

[Van T. Dinh]

Nope. First means the first in the internal order which we don't know. Use
Min or Max instead.

I never used First except in Totals ("Group By") Query where I know all the
values in the group are the same.

 

[Arvin Meyer]

Try using the TOP predicate in a query:

Select Top 1 From Table1 Order By FieldWhatever;
--
Arvin Meyer, MCP, MVP
Microsoft Access
Free Access downloads:
http://www.datastrat.com
http://www.mvps.org/access

 

[Not Me]

Try using the TOP predicate in a query:

Select Top 1 From Table1 Order By FieldWhatever;

Cheers, but it needs to work within a group by query. I want the top of each
group y'see...

Chris

 

[Rick Brandt]

Cheers, but it needs to work within a group by query. I want the top of each
group y'see...

Have you tried using Max()? First() and Last() are fairly useless except
for cases where you want a single value from the group and don't care which
one it is.

 

[Not Me]

Have you tried using Max()? First() and Last() are fairly useless except
for cases where you want a single value from the group and don't care which
one it is.

Thanks, that looks like the way to go, but now I'm onto another problem.
How can I display another column, and show the record in the same row as
that Max() field?

I.e.

101 5 hello
101 3 goodbye

group by first column, choose max of second column. How do I show "hello"
in the 3rd? (If I group by 3rd col I'll get dups?)

Cheers!
Chris

 

[Rick Brandt]

Thanks, that looks like the way to go, but now I'm onto another problem.
How can I display another column, and show the record in the same row as
that Max() field?

I.e.

101 5 hello
101 3 goodbye

group by first column, choose max of second column. How do I show "hello"
in the 3rd? (If I group by 3rd col I'll get dups?)

You can't do that in a single query. First create a query without the
third column and then use that query as a source for a second query where
you can add the third column in.

 

[Van T. Dinh]

See the techniques in The Access Web article:

http://www.mvps.org/access/queries/qry0020.htm

--
HTH
Van T. Dinh
MVP (Access)



news:[email protected]...

 

[Not Me]

See the techniques in The Access Web article:

http://www.mvps.org/access/queries/qry0020.htm

Best link in the world - ever )

Cheers,
Chris