SQL select data from row grouped by unique value

Question

I have a table like:

from |to|quani
A1   |A2|200  
B1   |B2|200  
C1   |C2|210  
D1   |D2|210  
E1   |E2|220  
F1   |F2|220  
G1   |G2|220
H1   |H2|200
J1   |J2|200

I need a query or a script to obtain something like this:

from|to|quani
A1  |B2|200  
C1  |D2|210  
E1  |G2|220
H1  |J2|200

If I use GROUP BY quani it selects

from|to|quani
A1  |A2|200  
C1  |C2|210  
E1  |E2|220

Possible duplicate of [SQL Group By with an Order By](https://stackoverflow.com/questions/27983/sql-group-by-with-an-order-by) — Mark Ryan Orosa, Nov 12 '17 at 11:04
The question is quite clear after the last edit except a typo in desired result. Unable to understand why its put on hold!! — Harshil Doshi, Nov 13 '17 at 06:09
Please don't edit your question to say that an answer gave you the solution. Instead, [**accept the answer**](/help/accepted-answer) that you found most helpful. — Donald Duck, Nov 13 '17 at 12:05
@DonaldDuck According to OP, he is unable to mark it as answer as the question is put on hold — Harshil Doshi, Nov 13 '17 at 20:54
@Harshil According to [this answer](https://meta.stackexchange.com/a/91830/349538), existing answers can still be marked as accepted when the question is closed. That the question is closed only means that new answers can't be posted, but existing answers can still be accepted. — Donald Duck, Nov 14 '17 at 18:55
@DonaldDuck Thank you for the info. I already asked OP to accept my answer. But looks like he's not aware of this too. — Harshil Doshi, Nov 14 '17 at 18:59

score 2 · Answer 1 · answered Nov 12 '17 at 11:10

2

seems you need the min for the values grouped by quani

select min(from), min(to), quani
from my_table 
group by quani

answered Nov 12 '17 at 11:10

ScaisEdge

131,976
10
91
107

when I group them I loose the row H1 |G2|200 – Ciubotaru Alexandru Nov 12 '17 at 12:19
2

then try explain in a clear wya the rules for obtain the result you are lokking for .. – ScaisEdge Nov 12 '17 at 12:53
1

Why `min(to)`? It's quite clear that OP wants max value of `to`. – Harshil Doshi Nov 13 '17 at 20:55

Harshil Doshi · Answer 2 · 2017-11-12T18:21:23.183

1

Try following query:

    select min(temp.from_) as from_,
       max(temp.to_) as to_,
       temp.quani as quani
       from
(SELECT t.*,
      (case when @prev = quani 
      then @rowid 
      else @rowid:=@rowid+1 
      end
      ) as rowid,
@prev:= quani as prev
FROM t, (SELECT @rowid:=0,@prev:=1) as init
ORDER BY from_) as temp
group by temp.quani,temp.rowid;

Updated DEMO

edited Nov 12 '17 at 18:21

answered Nov 12 '17 at 11:09

Harshil Doshi

3,497
3
14
37

Please try the edited query as well as check the demo. It should work exactly as you want. – Harshil Doshi Nov 12 '17 at 20:48
it is exactly what I need. thank you! – Ciubotaru Alexandru Nov 13 '17 at 10:05
Glad to help. Please mark it as answer, if you are still able to do it. Thank you. – Harshil Doshi Nov 13 '17 at 10:06
Unfortunately it was putted on hold, I can only to edit or delete the question. I think it have to remain as your solution may help others – Ciubotaru Alexandru Nov 13 '17 at 10:09
Exactly. Don't know why it's put on hold. – Harshil Doshi Nov 13 '17 at 10:12

score 0 · Answer 3 · answered Nov 12 '17 at 12:43

Assuming that you can order by the first column, you want the rows where quani changes. One method uses variables. Here is another method using correlated subqueries:

select t.*,
       (select t2.to
        from t t2
        where t2.to < t.to
        order by t2.to desc
        limit 1
       ) as prev_to
from t
having prev_to is null or prev_to <> to;

Notes:

Performance will not be great on largish tables, but an index on (to, quani) can help.
to and from are SQL keywords, so they are very bad names for columns.
The use of having without an aggregation is a MySQL extension that allows for filtering by column aliases (without a subquery).
The condition could also be written as where not prev_to <=> to, but the NULL-safe operator is less familiar than just <>.

SQL select data from row grouped by unique value

3 Answers3