Reading the min value from a table

[dr223]

Hallo,

I have the following table (Table1)

ename salary
a 100
b 200
c 300
d 400

and am supposed to bring the minimum salary value and ename, when a query is run. The following SQL syntax brings all the ename and salary, regardless inserting min..

SELECT Table1.ename, Min(Table1.salary) AS MinOfsalary
FROM Table1
GROUP BY Table1.ename;

Secondly, I have to write another query to show the third last salary and ename only from the list..

any ideas.. Thanks

 

[DCrake]

The query is correct as per your data. As A,B,C & D only appear once then the minimum will be the matching value. if you had

A 200
A 300
B 200
C 200

You would get
A 300
B 200
C 200

Where 200 is the smallest value found for A

 

[dr223]

Thanks for the reply, then what I want to do is show ONLY when the query is run

a 100

Because this is the ename with the lowest Salary.

Another query is to show the third lowest value,

i.e.,

c 300

Thanks and sorry for the confusion

 

[DCrake]

Then you want Min(Salary) first your query. What happens if more tham one have the same min salary?

 

[gemma-the-husky]

there is another current thread on a similar point

the problem is, is that oyu can find the min - but what you cant do is find other attributes relating ot the min (eg the name of the min)

as soon as you try to do this, you actually find the min for each name, which in this context is no help at all

--------
maybe another way to do this (if you only want 1 item form the whole table) is to have a query sorted in ascending order, based on this info

now if you do a dlookup it should find details from the first item in the sorted query.

(or open a recordset, and take the first item)

 

[dr223]

can you please direct me to the thread..it would be very helpful indeed

Thanks

 

[gemma-the-husky]

not sure how
does this do it?

http://www.access-programmers.co.uk/forums/showthread.php?t=167399

 

[dr223]

it doesnt do it really

 

[The_Doc_Man]

I think what you really want is to find the record where the salary equals the DMin of the salaries in that table, then display the record in question.

As to "third-last" - that's an insane request unless you go through the trouble of dynamically building a rank-order of the table first, and then search for the specific record by rank-order number. (Then hope you don't have duplicates.)

 

[Brianwarnock]

You can do this simply with cascading queries
query 1 finds the 3 lowest salaries

SELECT TOP 3 t1.salary
FROM t1
ORDER BY t1.salary

Query 2 takes in query1 and either selects MIN to give lowest salary or Max to get 3rd lowest salary

query3 takes in query2 joined to tableT1 on salary fields and selects the ename and the salary.

However none of this answers the question asked by Dcrake regarding more than 1 min salary.

Brian

 

[dr223]

When I inserted the code below the error "syntax error (missing operator) in query wexpression 'TOP 3 Table1.Salary'.

SELECT Table1.ename, TOP 3 Table1.Salary AS MinOfsalary
FROM Table1
ORDER BY Table1.Salary;


Whats wrong?

 

[Brianwarnock]

If you read my post again you will see that I did not select ename.

Brian

 

[dr223]

Thanks done

 

[Brianwarnock]

Good glad to have helped
You may have realised now that the min salary can be got with just 2 queries by selecting Min salary in the first. It can also be done with 1 query and a subquery. I'm not particularly good at sub queries but I think this will work

SELECT t1.ename, t1.salary
FROM t1
WHERE (((t1.salary)=(select min(t1.salary) as MinOfSalary from t1)));

Brian