Greatest Values Question

[Beerman3436]

If I using this line of code to try to pull out the top 5 values from a query:

.Source = "Select TOP 5 [Name],[SupDiff] From QryCover WHERE (((QryCover.SupDiff)>=2)) OR (((QryCover.SupDiff)<=-2)) ORDER By [Name]"

It works fine but what I need to know is how to find the greatest vlues in the field not just the first 5 it comes to.

It gives me this right now:
-2
+2
-2
+2
-8
But I know that later in the field there is a -5 value. How can I get it to look at the whole field and them take the top 5 values?

 

[namliam]

You are ordering by name, try ordering by SupDiff instead.

Regards

The Mailman

 

[Beerman3436]

Greatest Value Question

That is not the problem, the problem is that I have about 94 records in the field and it is getting the first 5 it comes to that are >=2 or <=-2.....

it finds now:

-2
+2
-2
+2
-8

And then in a field about 20 down from the last I see a -5....I need to know how to get the -5 and lose one of the -2's?

 

[Mile-O]

Those are the first 5 records returned by your query; these are the TOP 5. You need to sort by the field you want to get the Maximum 5 or the Minumum 5.

 

[FoFa]

.Source = "Select TOP 5 [Name],[SupDiff] From QryCover WHERE ABS(QryCover.SupDiff)>=2 ORDER By ABS(QryCover.SupDiff) DESC"

Rememeber top 5 means the first 5 rows returned, not largest valued 5.

 

[Beerman3436]

Greatest Values

ok here is and example:

I have

0
-1
-2
-8
-4
-5
-6
-7
-8
-8
-9
-9
-1
0
-2
-3


Out of that I need to get -7 -8 -8 -9 -9 and not just get the first 5 values.

 

[Mile-O]

Build a query that sorts the values and then build a second query that selects the Top 5.

 

[namliam]

Select TOP 5 [Name],[SupDiff] From QryCover WHERE QryCover.SupDiff>=2 OR QryCover.SupDiff<=-2 ORDER By [SupDiff] desc

That should do what you want, as i stated previously:

try ordering by SupDiff instead.

Regards

 

[Beerman3436]

Greatest Value Question

I still don't see the -5 value I need.

Out of the top 5 it comes out at number 7 behind

Showing up now:
-2
2
-2
2
-8

next 3 that will show up:
-2
-5
-2

As you can see that the -5 should show up in the list.

 

[Mile-O]

Based on the data you've give you won't get -5 at all as there are two -9 values and three -8 values.

 

[namliam]

Just quickly, before i lose it...

I created a table containing
Field1
-1
-2
-3
-4
-5
-6
-7
-8

Then did
SELECT TOP 5 Table1.Field1 FROM Table1 ORDER BY Table1.Field1 DESC
Returning
Field1
-1
-2
-3
-4
-5

So i changed Desc to Asc
SELECT TOP 5 Table1.Field1 FROM Table1 ORDER BY Table1.Field1 ASC
Returning:
Field1
-8
-7
-6
-5
-4

REGARDS

The Mailman "Delivers again!"

 

[Beerman3436]

Here is the list I have in the field:

SupDiff
0
0
0
0
-1
0
0
-2
0
0
-1
2
-1
0
1
0
-1
0
0
0
0
0
0
-1
0
1
-2
0
0
0
-1
0
0
0
0
0
2
0
0
0
-8
0
1
0
0
1
0
0
0
0
0
0
-2
0
-1
1
-5
0
0
-1
0
0
1
-1
0
0
0
0
-1
0
0
0
0
0
0
0
0
1
0
0
0
0
0
0
0
1
0
0
-2
0
0
0
0
-1

I need to find the 5 biggest values not just the top 5 on the top of the list.

 

[FoFa]

Of course, numeric negative numbers are less than positive numbers, but what beerman wanted was negative and postive numbers mixed together ranked by ABSOLUTE value, so -8 and +8 are at the same relative place in the sort chain, the only way to do this is to use ABSOLUTE value (or ABS).

 

[Tiro Cupidus]

Yeah, it seems that Fofa's first response to this thread is what you need to use. You say you don't want the first 5 records, but the largest values. Well, what you do is make the first 5 records the largest values then you're all squared away.

 

[Beerman3436]

No that did not work I still don't see the values after the -8.

 

[Tiro Cupidus]

I downloaded the database you posted and made a new query:

SELECT TOP 5 Name, Diff
FROM Query1
ORDER BY Abs(Diff) DESC;

This returned 8 records:

[b]Name	Diff[/b]
o1	-8
e2	-5
l	2
a2	-2
h	-2
k1	2
k3	-2
a1	-2

I didn't use the report because I'm missing a library or something for ADO.

 

[FoFa]

Post the SQL statement

 

[Beerman3436]

Greatest Values Questions

.Source = "Select TOP 5 [Name],[StuDiff] From QryCover WHERE (((QryCover.StuDiff)>=2)) OR (((QryCover.StuDiff)<=-2)) ORDER By ABS([StuDiff]) DESC"

I can get the numbers but now it needs to be in name order and not studiff. If I put it in name order then I get the wrong numbers.

 

[FoFa]

See query 3

 

[Beerman3436]

Still not right and there is no query3 in the database? I still can't see the -8 value?