[SOLVED] Auto increment on SELECT?

[Nhoj]

Not sure if its possible, but I have a table of categories with an autoincrement field, looks like this:

 

   

     

categoryID

     

categoryParent

     

categoryType

     

categoryName

   

   

     

1

     

0

     

1

     

Computers & Networking

   

   

     

2

     

0

     

1

     

Arts & Crafts

   

   

     

3

     

0

     

1

     

Music

   

   

     

4

     

0

     

1

     

Motor Vehicles

   

   

     

5

     

0

     

1

     

Books

   

   

     

6

     

0

     

1

     

Sporting Goods

   

   

     

7

     

2

     

1

     

Direct From Artist

   

   

     

8

     

2

     

1

     

Wholesale Lots

   

   

     

9

     

5

     

1

     

Fiction

   

   

     

10

     

5

     

1

     

Non-Fiction

   

 

 

The categoryID is the primary key set to auto_increment on insert, nothing special. What i'd like to be able to do, if possible, is when i select only a few, using a query like 'SELECT * FROM `categories` WHERE `categoryParent` = 0', have it pull the results with its unique ID and also have a temporary column with the result number it is.

 

So that previous query should pull something like the following:

   

     

temporary

     

categoryID

     

categoryParent

     

categoryType

     

categoryName

   

   

     

1

     

7

     

2

     

1

     

Direct From Artist

   

   

     

2

     

8

     

2

     

1

     

Wholesale Lots

   

   

     

3

     

9

     

5

     

1

     

Fiction

   

   

     

4

     

10

     

5

     

1

     

Non-Fiction

   

 

If anyone can enlighten, thanks!

 

Edit: The main idea behind it is to be able to sort the results ascending, descending, or even filter them via the category type and have that tempory column always increment by 1 starting from 1 and counting through the total, regardless of how it's filtered or sorted.

[Nhoj]

I figured it out; for thos that might be curious it worked as follows:

 

SET @rn :=0;

SELECT @rn := @rn +1 AS `tempID`, `categoryID`, `categoryParent`, `categoryType`, `categoryName`

FROM `categories`

WHERE `categoryParent` = 0

ORDER BY `categoryName` DESC