MYSQL enumeration: @rownum, odd and even records

Question

Hi,

I asked a question about creating temporary/ virtual ids for query results, http://stackoverflow.com/questions/4063998/mysql-php-temporary-virtual-ids-for-query-results/4064066#4064066

I nearly got I wanted with this link, http://craftycodeblog.com/2010/09/13/rownum-simulation-with-mysql/

I have managed to enumerate each row,

SELECT 
u.pg_id AS ID, 
u.pg_url AS URL,
u.pg_title AS Title,
u.pg_content_1 AS Content,
@rownum:=@rownum+1 AS rownum

FROM (
    SELECT pg_id, pg_url,pg_title,pg_content_1
    FROM root_pages

    WHERE root_pages.parent_id = '7'
    AND root_pages.pg_id != '7'
    AND root_pages.pg_cat_id = '2'
    AND root_pages.pg_hide != '1'

    ORDER BY pg_created DESC
) u,

(SELECT @rownum:=0) r

result,

ID  URL     Title   Content     rownum
53  a       x       x           1
52  b       x       x           2
43  c       x       x           3
41  d       x       x           4

but how can I work on it a bit further - I want to display the odd or even records only like the ones below - is it possible?

odd records,

ID  URL     Title   Content     rownum
53  a       x       x           1
43  c       x       x           3

even records,

ID  URL     Title   Content     rownum
52  b       x       x           2
41  d       x       x           4

thank you.

p.s. I don't quite understand the sql query actually even though I almost got the answer, for instance, what do the 'u' and 't' mean?

Answer 1

what do the 'u' and 't' mean?

They are table aliases, so you don't have to specify the entire name of the table when you need to make reference.

To get only the odd numbered records, use:

SELECT x.*
  FROM (SELECT u.pg_id AS ID, 
               u.pg_url AS URL,
               u.pg_title AS Title,
               u.pg_content_1 AS Content,
               @rownum := @rownum + 1 AS rownum
          FROM root_pages u
          JOIN (SELECT @rownum := 0) r
         WHERE u.parent_id = '7'
           AND u.pg_id != '7'
           AND u.pg_cat_id = '2'
           AND u.pg_hide != '1'
      ORDER BY u.pg_created DESC) x
WHERE x.rownum % 2 != 0

To get the even numbered records, use:

SELECT x.*
  FROM (SELECT u.pg_id AS ID, 
               u.pg_url AS URL,
               u.pg_title AS Title,
               u.pg_content_1 AS Content,
               @rownum := @rownum + 1 AS rownum
          FROM root_pages u
          JOIN (SELECT @rownum := 0) r
         WHERE u.parent_id = '7'
           AND u.pg_id != '7'
           AND u.pg_cat_id = '2'
           AND u.pg_hide != '1'
      ORDER BY u.pg_created DESC) x
WHERE x.rownum % 2 = 0

Explanation

The % is the modulus operator in MySQL syntax -- it returns the remainder of the division. For example 1 % 2 is 0.5, while 2 % 2 is zero. This is then used in the WHERE clause to filter the rows displayed.