CODEWITHSUNDEEP
pythonmysqlsql
Robert W. Hunter
Robert W. Hunter

Reputation: 3003

MySQL select number of element for field

I'm trying to get some data from MySQL which is structured like this

id  name        url
1   foo         SOMEURL
2   foo         SOMEURL
3   foo         SOMEURL
4   foo         SOMEURL
5   bar         SOMEURL
6   bar         SOMEURL
7   bar         SOMEURL
8   bar         SOMEURL
9   oop         SOMEURL
10  oop         SOMEURL
11  dpk         SOMEURL
12  more        SOMEURL

I want to get the number of item for name starting with 0, for example

id  name        url         number
1   foo         SOMEURL     0
2   foo         SOMEURL     1
3   foo         SOMEURL     2
4   foo         SOMEURL     3
5   bar         SOMEURL     0
6   bar         SOMEURL     1
7   bar         SOMEURL     2
8   bar         SOMEURL     3
9   oop         SOMEURL     0
10  oop         SOMEURL     1
11  dpk         SOMEURL     0
12  more        SOMEURL     0

But I don't know how to, the far I went was GROUP_CONCAT the url field and grouping by name so I get the number of total items, but not what I wanted (asked here)

To clarify, there are only three fields on my table, which are id name and url. The number column is generated through the query I'm asking for. I want that number start by 0 but it cant start in any number, it just have to reset on each different name, so I get like a one by one row count for each name... I think that the example is very clear.

Upvotes: 0

Views: 421

Answers (3)

Abhik Chakraborty
Abhik Chakraborty

Reputation: 44844

If you need to do it with mysql, then you can use dynamic variable. 

mysql> create table test (id int, name varchar(100));
Query OK, 0 rows affected (0.21 sec)

mysql> insert into test values (1,'foo'),(2,'foo'),(3,'foo'),(4,'bar'),(5,'oop'),(6,'bar'),(7,'oop');
Query OK, 7 rows affected (0.02 sec)
Records: 7  Duplicates: 0  Warnings: 0

select id,
name,
number 
from ( 
   select *, 
   @rn := if(@prev = name, @rn+1,0) as number , 
   @prev:=name 
   from test,(select @rn:=0,@prev:='')r order by name 
)t ;

The output will be as

+------+------+--------+
| id   | name | number |
+------+------+--------+
|    4 | bar  |      0 |
|    6 | bar  |      1 |
|    1 | foo  |      0 |
|    2 | foo  |      1 |
|    3 | foo  |      2 |
|    5 | oop  |      0 |
|    7 | oop  |      1 |
+------+------+--------+

This will display the data sort by name.

Upvotes: 3

davidvelilla
davidvelilla

Reputation: 488

With any programming language you can: (Note the following is pseudo-code very much like PHP but without actually writing all the code for connecting to your database, etc)

$numbers = array();
$output = array();
select all rows of your table
foreach row {
    if ( array_key_exists($row['name'], $numbers ) {
        $numbers[$row['name]]++;
    } else {
        $numbers[$row['name']] = 0;
    }
    $output[] = array(
        "id" => $row['id'],
        "name" => $row['name'],
        "url" => $row['url'],
        "number" => $numbers[$row['name']]
    );
}

At this point you will have an array that will look like:

[
    {"id": 1 , "name": "foo", "url": "SOMEURL", "number": 0],
    {"id": 2 , "name": "foo", "url": "SOMEURL", "number": 1],
    etc...
]

Upvotes: 0

Navjot Singh
Navjot Singh

Reputation: 514

select * from your_table group by url,name

Upvotes: 0

Related Questions