[ENG] record count

I've to retrieve in MySQL the record number (not the count()). I can't use
PHP for this, I absolutely need it in the mysql record.

with a table of 2 fields: firstname,lastname

if I do
I'd like this result in MYSQL :
select ???,firstname,lastname from person

1,bob,bedford
2,bob,dylan
3,paul,newmann

what should I put instead of the ??? in the query ? Count() would return the
total count of records, so what should I use ?

Thanks for help !

Bob

Re: [ENG] record count

Bob Bedford schrieb:
> I've to retrieve in MySQL the record number (not the count()). I can't use
> PHP for this, I absolutely need it in the mysql record.
>
> with a table of 2 fields: firstname,lastname
>
> if I do
> I'd like this result in MYSQL :
> select ???,firstname,lastname from person
>
> 1,bob,bedford
> 2,bob,dylan
> 3,paul,newmann
>
> what should I put instead of the ??? in the query ? Count() would return the
> total count of records, so what should I use ?

You could use a user variable like so

select @a:=@a+1, .... FROM table;

Of course, you'd have to initalize it first with
select @a := 0;

Re: [ENG] record count

Great, exactly what i meaned !

Thanks a lot Christian.


"Christian Kirsch" a écrit dans le message de news:
dnon14$1nr$1@news.mind.de...
> Bob Bedford schrieb:
>> I've to retrieve in MySQL the record number (not the count()). I can't
>> use
>> PHP for this, I absolutely need it in the mysql record.
>>
>> with a table of 2 fields: firstname,lastname
>>
>> if I do
>> I'd like this result in MYSQL :
>> select ???,firstname,lastname from person
>>
>> 1,bob,bedford
>> 2,bob,dylan
>> 3,paul,newmann
>>
>> what should I put instead of the ??? in the query ? Count() would return
>> the
>> total count of records, so what should I use ?
>
> You could use a user variable like so
>
> select @a:=@a+1, .... FROM table;
>
> Of course, you'd have to initalize it first with
> select @a := 0;
>

Re: [ENG] record count

Bob Bedford schrieb:
> Great, exactly what i meaned !
>

Fine.

Do you think you could get used to quote in a reasonable way, namely
put the quote first and answer it *afterwards*? We tend to read from
top to bottom, your way of quoting forces me to first read your
answer, than the original it refers to and then your answer again,
because I've forgotten what you said and completely lost the context.

Re: [ENG] record count

Bob Bedford wrote:
> I've to retrieve in MySQL the record number (not the count()). I can't
> use PHP for this, I absolutely need it in the mysql record.
>
> with a table of 2 fields: firstname,lastname
>
> if I do
> I'd like this result in MYSQL :
> select ???,firstname,lastname from person
>
> 1,bob,bedford
> 2,bob,dylan
> 3,paul,newmann
>
> what should I put instead of the ??? in the query ? Count() would return
> the total count of records, so what should I use ?

Simply add a column with an auto_increment (untested):

ALTER TABLE person ADD COLUMN counter int unsigned not null auto_increment;

And then:

SELECT counter, firstname, lastname FROM person;


Cheers,
Dominik
--
MonstersGame - Die Schlacht zwischen Vampiren und Werwölfen
http://spielwelt6.monstersgame.net/?ac=vid&vid=3018786

Re: [ENG] record count

Dominik Echterbruch schrieb:
> Bob Bedford wrote:
>
>>I've to retrieve in MySQL the record number (not the count()). I can't
>>use PHP for this, I absolutely need it in the mysql record.
>>
>>with a table of 2 fields: firstname,lastname
>>
>>if I do
>>I'd like this result in MYSQL :
>>select ???,firstname,lastname from person
>>
>>1,bob,bedford
>>2,bob,dylan
>>3,paul,newmann
>>
>>what should I put instead of the ??? in the query ? Count() would return
>>the total count of records, so what should I use ?
>
>
> Simply add a column with an auto_increment (untested):
>
> ALTER TABLE person ADD COLUMN counter int unsigned not null auto_increment;
>
> And then:
>
> SELECT counter, firstname, lastname FROM person;
>

That's not quite what the OP asked for (if I understood him correctly,
that is). He wanted the result set of *every* SELECT numbered starting
from 1. Your solution works fine as long as you SELECT all entries
from the table. It fails if only a subset is selected.

Re: [ENG] record count

"Christian Kirsch" a écrit dans le message de news:
dnonle$1o8$1@news.mind.de...
> Bob Bedford schrieb:
>> Great, exactly what i meaned !
>>
>
> Fine.
>
> Do you think you could get used to quote in a reasonable way, namely
> put the quote first and answer it *afterwards*? We tend to read from
> top to bottom, your way of quoting forces me to first read your
> answer, than the original it refers to and then your answer again,
> because I've forgotten what you said and completely lost the context.


Sorry, I wanted only to say thanks and did not remove your post.

Anyway, I've an other question: What if 2 people uses the same code at the
same time ?
I mean what if a set @a := 0; is executed when an other statement with the
select is being used by somebody else (the database is used in a web
server).

Bob

Re: [ENG] record count

Bob Bedford schrieb:

> Anyway, I've an other question: What if 2 people uses the same code at the
> same time ?
> I mean what if a set @a := 0; is executed when an other statement with the
> select is being used by somebody else (the database is used in a web
> server).
>

Don't know. Check the documentation at dev.mysql.com/doc on user
variables. If you dont' want to do that, just check it out on the
command line. No big deal.

My guess is that user variables are connection-specific.

Re: [ENG] record count

Christian Kirsch wrote:
>>
>>>if I do
>>>I'd like this result in MYSQL :
>>>select ???,firstname,lastname from person
>
[Code for adding an auto_increment]
>
> That's not quite what the OP asked for (if I understood him correctly,
> that is). He wanted the result set of *every* SELECT numbered starting
> from 1. Your solution works fine as long as you SELECT all entries
> from the table. It fails if only a subset is selected.

Well, he didn't mention a WHERE or ORDER BY clause :)


Grüße,
Dominik
--
MonstersGame - Die Schlacht zwischen Vampiren und Werwölfen
http://spielwelt6.monstersgame.net/?ac=vid&vid=3018786

Re: [ENG] record count

Christian Kirsch wrote:
>
>>Anyway, I've an other question: What if 2 people uses the same code at the
>>same time ?
>>I mean what if a set @a := 0; is executed when an other statement with the
>>select is being used by somebody else (the database is used in a web
>>server).
>
> Don't know. Check the documentation at dev.mysql.com/doc on user
> variables. If you dont' want to do that, just check it out on the
> command line. No big deal.
>
> My guess is that user variables are connection-specific.

They are, in fact. It's just like the well known last_insert_id thingy.
So be carefull when using persistent connections!


Grüße,
Dominik
--
MonstersGame - Die Schlacht zwischen Vampiren und Werwölfen
http://spielwelt6.monstersgame.net/?ac=vid&vid=3018786

Re: [ENG] record count

"Bob Bedford" wrote:

> I've to retrieve in MySQL the record number (not the count())

There ain't no such thing as an implicit record number in MySQL.
If you need such, create an id column explicitly:

ALTER TABLE person
ADD COLUMN id INT UNSIGNED NOT NULL AUTO_INCREMENT PRIMARY KEY

If you just want a running number (that increases with every row)
but is not connected with the record otherwise, you may go with
user variables:

SET @id=0;
SELECT @id:=(@id+1) AS id, firstname, lastname FROM person;


-> http://dev.mysql.com/doc/refman/4.1/en/variables.html


XL