The first() function

  • Thread starter Thread starter Not Me
  • Start date Start date
N

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
 
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.
 
Try using the TOP predicate in a query:

Select Top 1 From Table1 Order By FieldWhatever;
Click to expand...

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

Chris
 
Not Me said:
Cheers, but it needs to work within a group by query. I want the top of each
group y'see...
Click to expand...

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.
 
Rick Brandt said:
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.
Click to expand...

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
 
Not Me said:
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?)
Click to expand...

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.
 
Back
Top