CODEWITHSUNDEEP
sqlsqlitegrouping
Arthur
Arthur

Reputation: 1443

Grouping records in order (SQL)

I have a "call history" list in my application but i want to group all the calls as long as they are ordered (similar to android call history grouping).

What i mean is suppose we have this table:

id  |  number  |  time
------------------------
1   |  2222    |  00:00
2   |  2222    |  01:15
3   |  5555    |  02:00
4   |  2222    |  03:15
5   |  2222    |  05:16

What i need, is a result that will look something like this:

count  |  number
----------------
2      |  2222
1      |  5555
2      |  2222

But by using regular GROUP BY i get this result:

count  |  number
----------------
4      |  2222
1      |  5555

Can someone help me with the query please?

Upvotes: 2

Views: 189

Answers (1)

Joachim Isaksson
Joachim Isaksson

Reputation: 180897

It's kind of a complex query, but I'll at least make an attempt at explaining it ;-)

To get a good grouping, you can for each row count how many rows with a lesser time that have a number different from the current rows number. That will give all consecutive rows with the same number the same value since consecutive numbers will have the same amount of other numbers preceding them.

To make it really unique, just concatenate it with the number itself, which will allow you to group easily in an outer query.

SELECT COUNT(*) "count", MIN(id) starting_id, number
FROM (
  SELECT a.id, a.number, a.number || '-' || COUNT(b.id) seq
  FROM mytable a
  LEFT JOIN mytable b
    ON a.id > b.id AND a.number <> b.number
  GROUP BY a.id, a.number
)
GROUP BY seq
ORDER BY id;

An SQLfiddle to test with.

Upvotes: 4

Related Questions