MIN Function

  • Thread starter Thread starter Tom
  • Start date Start date
T

Tom

Instead of finding the "lowest" value of a records set (e.g. "5" out of a
record set "10, 5, 70, 25, 30, 100"), I'd like to find the "lowest three"
records.... "5, 10, 25".

Is that possible? Is yes, how would I go about it?

Thanks,
Tom
 
See whether you can use the TOP predicate.

Something like:

SELECT TOP 3 Field1, Field2, Field3
FROM MyTable
ORDER BY Field1 ASC

will give you the 3 rows associated with the lowest 3 values of Field1
 
Doug:

Thanks for the quick answer... I tried this and it doesn't give me the
results as expected... maybe I didn't provide enough details.

Using an example, MyTable will contain a single field (Field1) which has
e.g. 6 records.

10
5
70
25
30
100

I want to identify the 2nd, 1st, and 4th record (5, 10, 25) since they're
are the lowest 3 out of 6 records. Do you have any additional pointers as
to how I might query for that?

Thanks again,
Tom
 
Doug:

Thanks for the quick answer... I tried this and it doesn't give me the
results as expected... maybe I didn't provide enough details.

Using an example, MyTable will contain a single field (Field1) which has
e.g. 6 records.

10
5
70
25
30
100

I want to identify the 2nd, 1st, and 4th record (5, 10, 25) since they're
are the lowest 3 out of 6 records. Do you have any additional pointers as
to how I might query for that?

Thanks again,
Tom
Click to expand...

Perhaps you are doing something wrong, as it should work.
Copy and post your exact SQL.
 
You want to "identify the 2nd, 1st, and 4th record (5, 10, 25)"

Do you want to see
5
10
15

or See this?
10 <<<
5 <<<
70
25 <<<
30
100

The former is Tom's post.

The latter you can get by using an OUTER JOIN to Tom's query.

Stephen Rasey
Houston
http://wiserways.com
http://excelsig.org
 
Fred:

Thanks for chipping in... here's the code

SELECT TOP 3 Field1, Field2, Field3
FROM MyTable
ORDER BY Field1 ASC

resulting in:

Field1 Field2 Field3
10 15 100
35 78 8
75 25 98



In the MyTable I added additional values since I figured that I needed it
based on the Doug's query suggestion.

Still confused!?!?

Tom
 
Well then Doug's query

SELECT TOP 3 Field1
FROM MyTable
ORDER BY Field1 ASC;
should work.

Doug had Field1, Field2, Field3. maybe that was confusing

I honestly do not know how to easily return the results as
5, 10, 25 in three columns.

Stephen Rasey
Houston
 
Back
Top