Virtuel-Counting-Row

[walexand]

I use the database mysql v.4.

My problem is...
I have a select like:
select * from user where language = "de";

the result are then:
id name
=============== ====
1 max
5 tim
99 otto

so and what i need is a virtuell row (pos) with a counting index of the result:
id name pos
=============== ==== ===
1 max 1
5 tim 2
99 otto 3

how can i make this with mysql?
My first idea was to made that with views but mysql can't do that!

MFG Alexander W.

[Siemel Naran]

<wa******@web.d e> wrote in message

I have a select like:
select * from user where language = "de";

the result are then:
id name
=============== ====
1 max
5 tim
99 otto

so and what i need is a virtuell row (pos) with a counting index of the result: id name pos
=============== ==== ===
1 max 1
5 tim 2
99 otto 3

how can i make this with mysql?
My first idea was to made that with views but mysql can't do that!

There might be some special function, I don't know for sure. But in 5.0
they will have cursors, and in the present version you can always do it in
C++ and other languages.

I can think of trying an extra table, but it doesn't seem like the best
solution.

create table temp_product (
id int(1) unsigned auto_increment,
product_id int(1) unsigned not null references product(id),
primary key (id)
);

....

delete from temp_product;

insert into temp_product (product_id)
select id from product
order by name desc;

select temp_product.id , product.*
from product inner join temp_product on product.id =
temp_product.pr oduct_id;

[Siemel Naran]

<wa******@web.d e> wrote in message

I have a select like:
select * from user where language = "de";

the result are then:
id name
=============== ====
1 max
5 tim
99 otto

so and what i need is a virtuell row (pos) with a counting index of the result: id name pos
=============== ==== ===
1 max 1
5 tim 2
99 otto 3

how can i make this with mysql?
My first idea was to made that with views but mysql can't do that!

There might be some special function, I don't know for sure. But in 5.0
they will have cursors, and in the present version you can always do it in
C++ and other languages.

I can think of trying an extra table, but it doesn't seem like the best
solution.

create table temp_product (
id int(1) unsigned auto_increment,
product_id int(1) unsigned not null references product(id),
primary key (id)
);

....

delete from temp_product;

insert into temp_product (product_id)
select id from product
order by name desc;

select temp_product.id , product.*
from product inner join temp_product on product.id =
temp_product.pr oduct_id;

[Mike Chirico]

<wa******@web.d e> wrote in message
news:b1******** *************** ***@posting.goo gle.com...

I use the database mysql v.4.

My problem is...
I have a select like:
select * from user where language = "de";

the result are then:
id name
=============== ====
1 max
5 tim
99 otto

so and what i need is a virtuell row (pos) with a counting index of the result: id name pos
=============== ==== ===
1 max 1
5 tim 2
99 otto 3

Assume the following table:

CREATE TABLE student (
s_id int(11) NOT NULL auto_increment,
name varchar(15),
PRIMARY KEY (s_id)
);

Add some rows and delete (just to convice you this works in all cases
independent of the primary key)

insert into student (name) values ('tom');
insert into student (name) values ('bob');
insert into student (name) values ('sue');
insert into student (name) values ('mike');
insert into student (name) values ('joe');
insert into student (name) values ('zoe');
insert into student (name) values ('harpo');

delete from student where name = 'bob';

Now, note mc is the row count...indepen dent of s_id;
select a.name,sum(1) as mc
from student a, student b
where a.s_id <= b.s_id
group by a.s_id, a.name order by mc;
+-------+------+

| name | mc |
+-------+------+
| harpo | 1 |
| zoe | 2 |
| joe | 3 |
| mike | 4 |
| sue | 5 |
| tom | 6 |
+-------+------+
6 rows in set (0.00 sec)

Reference (TIP 22)
http://osdn.dl.sourceforge.net/sourc...ADME_mysql.txt

Hope this helps,

Mike Chirico

[Mike Chirico]

<wa******@web.d e> wrote in message
news:b1******** *************** ***@posting.goo gle.com...

I use the database mysql v.4.

My problem is...
I have a select like:
select * from user where language = "de";

the result are then:
id name
=============== ====
1 max
5 tim
99 otto

so and what i need is a virtuell row (pos) with a counting index of the result: id name pos
=============== ==== ===
1 max 1
5 tim 2
99 otto 3

Assume the following table:

CREATE TABLE student (
s_id int(11) NOT NULL auto_increment,
name varchar(15),
PRIMARY KEY (s_id)
);

Add some rows and delete (just to convice you this works in all cases
independent of the primary key)

insert into student (name) values ('tom');
insert into student (name) values ('bob');
insert into student (name) values ('sue');
insert into student (name) values ('mike');
insert into student (name) values ('joe');
insert into student (name) values ('zoe');
insert into student (name) values ('harpo');

delete from student where name = 'bob';

Now, note mc is the row count...indepen dent of s_id;
select a.name,sum(1) as mc
from student a, student b
where a.s_id <= b.s_id
group by a.s_id, a.name order by mc;
+-------+------+

| name | mc |
+-------+------+
| harpo | 1 |
| zoe | 2 |
| joe | 3 |
| mike | 4 |
| sue | 5 |
| tom | 6 |
+-------+------+
6 rows in set (0.00 sec)

Reference (TIP 22)
http://osdn.dl.sourceforge.net/sourc...ADME_mysql.txt

Hope this helps,

Mike Chirico

[Siemel Naran]

"Mike Chirico" <mc******@comca st.net> wrote in message
news:L66dncvnK4 TEdAfdRVn-

select a.name,sum(1) as mc
from student a, student b
where a.s_id <= b.s_id
group by a.s_id, a.name order by mc;
That's pretty cool!
Reference (TIP 22)
http://osdn.dl.sourceforge.net/sourc...ADME_mysql.txt

[Siemel Naran]

"Mike Chirico" <mc******@comca st.net> wrote in message
news:L66dncvnK4 TEdAfdRVn-

select a.name,sum(1) as mc
from student a, student b
where a.s_id <= b.s_id
group by a.s_id, a.name order by mc;
That's pretty cool!
Reference (TIP 22)
http://osdn.dl.sourceforge.net/sourc...ADME_mysql.txt

[Mike Chirico]

"Siemel Naran" <Si*********@RE MOVE.att.net> wrote in message
news:Vw******** ************@bg tnsc05-news.ops.worldn et.att.net...

"Mike Chirico" <mc******@comca st.net> wrote in message
news:L66dncvnK4 TEdAfdRVn-

select a.name,sum(1) as mc
from student a, student b
where a.s_id <= b.s_id
group by a.s_id, a.name order by mc;

That's pretty cool!

Reference (TIP 22)
http://osdn.dl.sourceforge.net/sourc...ADME_mysql.txt

It's also possible to compute running aggregates. Assume the following
table
and data:

CREATE TABLE dice (
d_id int(11) NOT NULL auto_increment,
roll int,
PRIMARY KEY (d_id)
);

insert into dice (roll) values (1);
insert into dice (roll) values (2);
insert into dice (roll) values (3);
insert into dice (roll) values (4);
insert into dice (roll) values (5);
insert into dice (roll) values (6);
The following gives a running, cumulative, sum:

mysql> select a.roll, sum(b.roll) from dice a, dice b
where b.d_id <= a.d_id group by a.d_id, a.roll;

+------+-------------+

| roll | sum(b.roll) |
+------+-------------+
| 1 | 1 |
| 2 | 3 |
| 3 | 6 |
| 4 | 10 |
| 5 | 15 |
| 6 | 21 |
+------+-------------+
6 rows in set (0.00 sec)
To get a running average change the
sum to avg as follows:
mysql> select a.roll, avg(b.roll) from dice a, dice b
where b.d_id <= a.d_id group by a.d_id, a.roll;

+------+-------------+
| roll | avg(b.roll) |
+------+-------------+
| 1 | 1.0000 |
| 2 | 1.5000 |
| 3 | 2.0000 |

| 4 | 2.5000 |
| 5 | 3.0000 |
| 6 | 3.5000 |
+------+-------------+
6 rows in set (0.00 sec)
Regards,

Mike Chirico

Reference (TIP 23)
http://osdn.dl.sourceforge.net/sourc...ADME_mysql.txt

[Mike Chirico]

"Siemel Naran" <Si*********@RE MOVE.att.net> wrote in message
news:Vw******** ************@bg tnsc05-news.ops.worldn et.att.net...

"Mike Chirico" <mc******@comca st.net> wrote in message
news:L66dncvnK4 TEdAfdRVn-

select a.name,sum(1) as mc
from student a, student b
where a.s_id <= b.s_id
group by a.s_id, a.name order by mc;

That's pretty cool!

Reference (TIP 22)
http://osdn.dl.sourceforge.net/sourc...ADME_mysql.txt

It's also possible to compute running aggregates. Assume the following
table
and data:

CREATE TABLE dice (
d_id int(11) NOT NULL auto_increment,
roll int,
PRIMARY KEY (d_id)
);

insert into dice (roll) values (1);
insert into dice (roll) values (2);
insert into dice (roll) values (3);
insert into dice (roll) values (4);
insert into dice (roll) values (5);
insert into dice (roll) values (6);
The following gives a running, cumulative, sum:

mysql> select a.roll, sum(b.roll) from dice a, dice b
where b.d_id <= a.d_id group by a.d_id, a.roll;

+------+-------------+

| roll | sum(b.roll) |
+------+-------------+
| 1 | 1 |
| 2 | 3 |
| 3 | 6 |
| 4 | 10 |
| 5 | 15 |
| 6 | 21 |
+------+-------------+
6 rows in set (0.00 sec)
To get a running average change the
sum to avg as follows:
mysql> select a.roll, avg(b.roll) from dice a, dice b
where b.d_id <= a.d_id group by a.d_id, a.roll;

+------+-------------+
| roll | avg(b.roll) |
+------+-------------+
| 1 | 1.0000 |
| 2 | 1.5000 |
| 3 | 2.0000 |

| 4 | 2.5000 |
| 5 | 3.0000 |
| 6 | 3.5000 |
+------+-------------+
6 rows in set (0.00 sec)
Regards,

Mike Chirico

Reference (TIP 23)
http://osdn.dl.sourceforge.net/sourc...ADME_mysql.txt

[Siemel Naran]

"Siemel Naran" <Si*********@RE MOVE.att.net> wrote in message
news:VwCmc.2716 7

"Mike Chirico" <mc******@comca st.net> wrote in message

select a.name,sum(1) as mc
from student a, student b
where a.s_id <= b.s_id
group by a.s_id, a.name order by mc;

Reference (TIP 22)
http://osdn.dl.sourceforge.net/sourc...ADME_mysql.txt

That's pretty cool!

Instead of sum(1) you could use count(*).

What's the running time of the algorithm? Because it selects from 2 tables
it seems a O(N^2). If the system uses the index on s_id it can eliminate
the inner loop and immediately find the records of interest -- for example,
if driving by alias 'a', it can immediately find the records in alias 'b'
meeting the condition in the where clause. But it still has to loop through
these records in order to determine the count(*) or sum(1), because this
informations is (probably) not available in the index. Which makes it
O(N^2) still. Of course, if N is small as in the examples in this thread,
then it doesn't matter.