1

I am not great at SQL queries so I thought I'd ask here. I have a table my_table:

NOTE : Consider all the columns as strings. I just represent them as numbers here for a better understanding.

A B C
-----
1 2 3
2 2 3
2 5 6
3 5 6

I want the result to be-

A B C
-----
1 2 3
2 5 6

So basically, dropping duplicate pairs for B, C, and taking the first occurrence of A for that pair of B, C.

Barbaros Özhan
  • 59,113
  • 10
  • 31
  • 55
kev
  • 2,741
  • 5
  • 22
  • 48
  • Welcome to StackOverflow! Don't hesitate on looking thru the [tour](https://stackoverflow.com/tour) or in the [help](https://stackoverflow.com/help/mcve) to be sure about how to ask questions. Asking something like "I want to do xyz" with no attempts of code included or info about the errors you got, sample data and desired results is likely to be closed due to lack of info to answer. Remember to add all the relevant code, error logs and everything in your question as plain text, so we can provide better help. :) – Alfabravo Nov 01 '19 at 16:55
  • Is there also a column like `id`? – forpas Nov 01 '19 at 16:56
  • is there a rule for ordering ? – Barbaros Özhan Nov 01 '19 at 16:56
  • @forpas no other column – kev Nov 01 '19 at 17:05
  • @BarbarosÖzhan no rule for ordering, you can order the final result however you want – kev Nov 01 '19 at 17:06
  • Change your table schema to add an auto increment column. Then it is easy to take the *1st occurrence*. – forpas Nov 01 '19 at 17:17
  • Adding an autoincrement value (like `forpas`) suggested will make the problem much easier, and simpler. But do you REALLY want the first value of A? of the minimal value of A (see answer below)? – Luuk Nov 01 '19 at 17:20

4 Answers4

1

Seems you need to consider the minimum of the column A and grouping by B and C :

select min(cast(A as unsigned)) as A, cast(B as unsigned) as B, cast(C as unsigned) as C
  from my_table
 group by B , C

cast(<column> as unsigned) conversion is used to make them numeric.

Demo

Barbaros Özhan
  • 59,113
  • 10
  • 31
  • 55
0

first, you'd better post some of the something you tried here.

In mysql 8.0 you can use row_number() over (partition by B, C order by A) to slove this question.

CREATE TABLE Table1
  (`A` int, `B` int, `C` int)
;

INSERT INTO Table1
  (`A`, `B`, `C`)
VALUES
  (1, 2, 3),
  (2, 2, 3),
  (2, 5, 6),
  (3, 5, 6)
;

select `A`, `B`, `C` from (
    select *,row_number() over (partition by `B`, `C` order by `A`) rnk from Table1
) T
where rnk = 1;

 A |  B |  C
-: | -: | -:
 1 |  2 |  3
 2 |  5 |  6

db<>fiddle here

if mysql < 8.0 you can follow this answer ROW_NUMBER() in MySQL

Update :

if like @forpas says : taking the first occurrence of A for that pair of B, C is not solved by order by A.

You have to sort the rownum first : 

CREATE TABLE Table1
  (`A` int, `B` int, `C` int)
;

INSERT INTO Table1
  (`A`, `B`, `C`)
VALUES
  (2, 2, 3),
  (1, 2, 3),
  (2, 5, 6),
  (3, 5, 6)
;

SET @rownum:=0;

select `A`, `B`, `C` from (
    select *,row_number() over (partition by `B`, `C` order by rownum) rnk from (
        select *,@rownum:=@rownum+1 AS rownum from Table1
    ) T
) T
where rnk = 1;

✓

 A |  B |  C
-: | -: | -:
 2 |  2 |  3
 2 |  5 |  6

db<>fiddle here

Wei Lin
  • 3,591
  • 2
  • 20
  • 52
0

You seem to want aggregation:

select min(a) as a, b, c
from t
group by b, c;

I assumes "first" means the minimum value of a. SQL tables represent unordered sets, so that seems like the most sensible interpretation.

Gordon Linoff
  • 1,242,037
  • 58
  • 646
  • 786
0
select min(A) as A,B,C 
from Table1
group by B,C

As, you requested this will get the minimum value of A for combinations of B and C.

Luuk
  • 12,245
  • 5
  • 22
  • 33