0

I have a MySQL table with two columns: a and b. There are 20 entries in that table

For 10 entries, a = 3. For the other 10 entries, a = 4.

I want to select rows such that b = 0, but I only want one from the group where a = 3, and one from the group a = 4.

The base query would be something like:

SELECT * FROM `table_name` WHERE b = 0 AND rest_of_query

What should rest_of_query be?

JohnFx
  • 34,542
  • 18
  • 104
  • 162
Sean Carruthers
  • 414
  • 2
  • 10
  • Do u want the first matching it or a random one ? – kritya Sep 17 '11 at 04:57
  • Can you use more descriptive column names for your examples? If you only want one of the a = 3 and a = 4s, what criteria do you want to use to determine which one to return. Does it matter which one? – six8 Sep 17 '11 at 04:57
  • http://stackoverflow.com/questions/2129693/mysql-using-limit-within-group-by-to-get-n-results-per-group seems to cover the same idea – Jason Sperske Sep 17 '11 at 05:00

4 Answers4

0

Assuming that you only need the first from both the group. You can do this by:

SELECT * FROM `table_name` WHERE b=0 AND a=3 LIMIT 1
UNION
SELECT * FROM `table_name` WHERE b=0 AND a=4 LIMIT 1;
kritya
  • 3,312
  • 7
  • 43
  • 60
0

Given that you haven't specified any particular criteria for the additional columns (if any), the following query will provide what you want. The specific rows selected will probably be non-deterministic.

select * from `table_name` where b=0 group by a;
Steffan Mejia
  • 48
  • 3
0

From what I understand You Only have 2 columns, and are defining what one of them shoudld be and then you want only distinct entries for the other column.

This can be done by GROUP BY as

SELECT a, b FROM table_name WHERE b = 0 GROUP BY a;
danishgoel
  • 3,650
  • 1
  • 18
  • 30
0

UNION query should be like this 

(SELECT * FROM `table_name` WHERE b=0 AND a=3 LIMIT 1)
UNION
(SELECT * FROM `table_name` WHERE b=0 AND a=4 LIMIT 1);
Jyoti Ranjan
  • 703
  • 2
  • 13
  • 29