Incorrect first() behavior even though normal order shows correct order

  • Thread starter William E Hannon Jr
  • Start date
W

William E Hannon Jr

The query below shows everything in the right order for
3329132335 52503
then
3329132335 52504

Then when I switch to Total function of first it randomly
give me the incorrect Index. The problem occurs consistently
for the same data, yet when I reload the data the problem
shifts around.

SELECT log.time_decimal, First(log.Index) AS FirstOfIndex
FROM log
GROUP BY log.time_decimal
ORDER BY log.time_decimal, First(log.Index);

time_decimal FirstOfIndex
....
3329132325 52502
3329132335 52504
3329132337 52505
....

Is there any other way around this??
Or will I be forced to go through each record and check for the
highest (max) decimal value and store it myself??


---
SELECT log.time_decimal, log.Index
FROM log
GROUP BY log.time_decimal, log.Index
ORDER BY log.time_decimal, log.Index;

time_decimal Index
....
3329132325 52502
3329132335 52503
3329132335 52504
3329132337 52505
....
 
S

Steve Schapel

William,

To be honest, I am struggling to understand what you are trying to
achieve. At first, I thought you wanted to return the smallest Index
for each Time_decimal. If that is the case, you need the Min function
instead of First, like this...
SELECT log.time_decimal, Min(log.Index) AS MinIndex
FROM log
GROUP BY log.time_decimal
ORDER BY log.time_decimal

However, then you referred to the "highest (max) decimal value", so I
guess I may have misinterpreted your requirement.
 
W

William E Hannon Jr

Thank you Min() worked ..... I thought First() should have worked since
it was first in sorted order but it was too buggy.

To explain the "highest (max) decimal value. Getting valid time_decimal
and index was the first step, the second second step was then use the index
in SQL update criteria to update a different column with the "highest time
decimal value known at time.

Once again Thank You.
 

Ask a Question

Want to reply to this thread or ask your own question?

You'll need to choose a username for the site, which only take a couple of moments. After that, you can post your question and our members will help you out.

Ask a Question

Top