php sql query to pick a row data where a field value is maximum

Question

How can I select and display value of that field in a row of a table whose value is maximum or minimum ?

For example, say I have the following table structure for balance.

+------+-------+--------+
| id   | bal   | userid |
+------+-------+--------+
| 1    | 4.00  | 1      |
| 2    | 8.35  | 2      |
| 3    | 15.67 | 3      |
| 4    | 10.00 | 4      |
+------+-------+--------+

Here, I want to show the users with maximum balance and minimum balance. What should be my query to pick show them? Like here in this case user 3 has maximum balance and user 1 has minimum. I want to pick them and LEFT JOIN them with my members table using mem_id to extract their username mem_uname and show their balance.

Answer 1

One option would be the following union query:

(SELECT *, 'max user' AS type FROM balance ORDER BY bal DESC LIMIT 1)
UNION ALL
(SELECT *, 'min user' FROM balance ORDER BY bal LIMIT 1)

This assumes that you don't need to worry about ties for greatest/least balance. If this be a concern, then we would have to do more work:

SELECT *, 'max users' AS type
FROM balance
WHERE bal = (SELECT MAX(bal) FROM balance)
UNION ALL
SELECT *, 'min users'
FROM balance
WHERE bal = (SELECT MIN(bal) FROM balance)
ORDER BY type;

Answer 2

try the following query

(select * from balance where bal=(select max(bal) from balance))
union all
(select * from balance where bal=(select min(bal) from balance));

OUT PUT

+------+-------+--------+
| id   | bal   | userid |
+------+-------+--------+
|    3 | 15.67 |      3 |
|    1 |     4 |      1 |
+------+-------+--------+