Geek asks and answers

Select * where id in (....) and all columX are equal

People hej,

I have to deal with a special problem. This is a simplified table first:

 id | A | B
-------------
  1 | a | 1
  2 | b | 2
  3 | c | 1
  4 | d | 1
  5 | e | 3
  6 | f | 1
  7 | g | 2

thanks to a rather lame API, I get a list of required identifiers, for example

(1,2,3,5,6)

BUT, from this list I should select only rows with a certain unknown value in column B. In this example, the unknown value will be 1, since the first matching identifier has 1 in column B.

As a result, I will need strings

1, 3, 4, 6

because the required rows 2 and 6 have different meanings in column B.

Hope you can follow it so far.

The problem has already been solved by filtering the result inside the software, but I wonder if there is a solution with pure SQL?

- MySQL, API, : (

! , , , , SQL- .

P.S.

:

 (2,3,5,6,7)

:

(2, 7)

B "2"

+3
3

:

SELECT * FROM Table WHERE id IN (1,2,3,4,5) AND ColB = (SELECT ColB FROM Table WHERE id IN (1,2,3,4,5) ORDER BY id ASC LIMIT 1)

( ) , , ​​ script, PHP. , .

, (.. 3,2,7,1, 3 , 1).

. , :

SELECT * FROM Table WHERE id IN (1,2,3,4,5) AND ColB = (SELECT ColB FROM Table WHERE id IN (1,2,3,4,5) ORDER BY FIELD(id,1,2,3,4,5) LIMIT 1)
+2

.

B. , .

+1

You can use a subquery that returns the value of the first (lowest) identifier using the LEAST function:

SELECT
  *
FROM
  yourtable
WHERE
  B = (SELECT B FROM yourtable WHERE ID = LEAST(1,2,3,5,6));

See the fiddle here . You can also add a condition:

WHERE
  ID IN (1,2,3,5,6)
  AND
  B = (SELECT B FROM yourtable WHERE ID = LEAST(1,2,3,5,6));
0
source

More articles:


All Articles