Temporary numbering in a query 1

[kupe]

Is there a way to add a number column in a query, please, experts? As in

SELECT id, FName, SName, Date
FROM table
WHERE forum = 'TekTips';

But to add a column that acts as a sort of temporary id, 1 - 25 whatever, only for use on that one particular occasion.

 

[TonyGroves]

You mean you want the returned records to be numbered from 1 onwards? You could use:
[tt]
@n=0;
SELECT @n:=@n+1 recnum,id,fname,sname,date
FROM tablename
WHERE forum='tektips';
[/tt]

 

[kupe]

Many thanks, Tony. This is somewhere I have not been before.

@n=0;
SELECT @n:=@n+1 recnum,id, about
FROM post;


My recnum column returns Null. Something I should add?

 

[TonyGroves]

The @n:=0 statement initialises the counter. Maybe your interface doesn't handle multiple queries at once. If you issue two separate queries, the first being @n:=0; and the second being the SELECT, that should work.

 

[TonyGroves]

Sorry, I'm half-asleep! You can forget my last post.

The syntax of the first line should have been:
[tt]
SET @n=0;
[/tt]

 

[kupe]

Excellent!

Stand on Thursday, and you'll get my vote. (Perhaps not quite as flattering as I mean when the others are considered.)

That is really good - and rather further on than my present level. (I can imagine I am advancing.)

Presumably, I would declare what I think must be a variable whenever I would like to use that numbering idea?

Many thanks, Master.

 

[TonyGroves]

The @n (it doesn't have to be @n, you can call it anything you like) is a variable which you can manipulate. It doesn't need to be pre-declared, but is automatically initialised to NULL unless you assign it a value. Variables like that are local to the current session, so they retain their value between queries, and lose it when you disconnect.

(What's on on Thursday?)

 

[kupe]

General Election! I hope you are voting. England is in trouble and needs all voters (assuming, of course, you are based in this sceptre'd isle).

All noted about the variables. Much obliged.

 

[TonyGroves]

Oh yes, I've heard about that.

I'm not based there though so I can't vote. I'm in Ireland.

 

[kupe]

Lucky man. Thursday evening will show whether any democracy remains in the world.

Thanks for all your help, Tony.