CODEWITHSUNDEEP
mysqlsql
Nikki
Nikki

Reputation: 3

SQL: Select an item in one column based on the other column's value

I have a table that looks like this:

+-----+----+
|A    |B   |
+-----+----+
|    1|   0|
|    2|   1|
|    2|   1|
|    2|   1|
|    2|   2|
|    3|   1|
|    4|   1|
|    5|   1|
|    5|   2|
|    6|   2|
|    7|   2|
|    8|   2|
+-----+----+

I want to select values of A, where the value of B is the lowest. but want to keep the repetitions. Say I have 3 pairs such that (4,5),(4,5),(4,6), I want the result to be (4,5), (4,5).

For the above table, I would like the output to be

+-----+----+
|A    |B   |
+-----+----+
|    1|   0|
|    2|   1|
|    2|   1|
|    2|   1|
|    3|   1|
|    4|   1|
|    5|   1|
|    6|   2|
|    7|   2|
|    8|   2|
+-----+----+

I was trying to do something like this, but I'm lost!

SELECT t1.A, t1.B from table t1 JOIN table t2
WHERE t1.B >= t2.B
ORDER BY B DESC

Any leads appreciated!

Upvotes: 0

Views: 623

Answers (3)

Suraj
Suraj

Reputation: 3137

You can use subquery. First identify all the columns that are satisfying the condition. Then filter out the records using where.

select A,B from(
        select A,B, case when B<A then 'Y' else 'N' end as C from table)
        where C = 'Y'

Upvotes: 0

Jay Shankar Gupta
Jay Shankar Gupta

Reputation: 6098

SELECT
a.A, a.B
FROM table1 a WHERE B=(select MIN(b) FROM table1)
ORDER BY B DESC

Upvotes: 0

isaace
isaace

Reputation: 3429

This should work:

select t1.* from mytable t1
join
    (select A, min(B) as B from mytable group by A) t2
on t1.A = t2.A and t1.B = t2.B

Upvotes: 1

Related Questions