mysql & php: temporary/ virtual ids for query results?

Question

I wonder if it is possible to assign temporary/ virtual IDs for a query result?

For instance, I have this as my query,

SELECT 

pg_id AS ID,
pg_url AS URL,
pg_title AS Title,
pg_content_1 AS Content

FROM root_pages

ORDER BY pg_created DESC

output:

ID  URL     Title   Content 
53  a       A       xxx 
40  b       B       xxx 
35  c       C       xxx

you can see the the gap between the IDs - they are very untidy. I wonder if I can make a virtual column or something so that I have the output below,

ID  URL     Title   Content  Virtual ID
53  a       A       xxx      3
40  b       B       xxx      2
35  c       C       xxx      1

from a query like this below,

SELECT 

pg_id AS ID,
pg_url AS URL,
pg_title AS Title,
pg_content_1 AS Content

FROM root_pages

ORDER BY virtual_id DESC

is it possible??

thanks!

yes it can with the id alone. but it doesnt output 'correctly' when I if I query the odd IDs or even IDs - WHERE MOD(pg_id,2) = 1. — Run, Oct 31 '10 at 17:02

score 0 · Answer 1 · edited May 23 '17 at 12:26

0

If you still persist on getting the position of each row take a look at this answer

edited May 23 '17 at 12:26

Community

answered Oct 31 '10 at 16:57

Breezer

thanks. but it is a single row output - not what I am looking for I am afraid... cheers – Run Oct 31 '10 at 17:09
if you remove the WHERE t.name = 'Beta' then you get data for all the rows? – Breezer Oct 31 '10 at 17:18
I tried with this (SELECT COUNT(*) FROM root_pages) AS position. then it creates a same newest number for each row... – Run Oct 31 '10 at 17:35
i have found a solution - http://craftycodeblog.com/2010/09/13/rownum-simulation-with-mysql/ thanks for your replies that led me to it! :) – Run Oct 31 '10 at 18:18

1 Answers1