SitePoint Sponsor

User Tag List

Results 1 to 5 of 5
  1. #1
    SitePoint Evangelist webchalkboard's Avatar
    Join Date
    Jan 2005
    Location
    Bristol, UK
    Posts
    494
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

    Sort by effecting number of rows returned

    Hi,

    I've just discovered something extremely strange and I can't work out what on earth could be the reason for it.

    If I do this query.

    Code:
    SELECT *
    FROM `cashback`
    WHERE `user_id` =1912
    ORDER BY `user_id` ASC
    I get 1037 rows returned

    However if I sort by a different column

    Code:
    SELECT *
    FROM `cashback`
    WHERE `user_id` =1912
    ORDER BY `epi_report_id` ASC
    Now I get 1204 rows returned.

    How can this possibly be? Surely the order you get the columns returned should not effect the number of rows returned!?

    Am I missing something really silly here or am I in the twilight zone?

    Thanks,
    Tom
    Websites for Sale - Sell websites in a purpose built marketplace
    Then do some Shopping

  2. #2
    SitePoint Wizard guelphdad's Avatar
    Join Date
    Oct 2003
    Location
    St. Catharines, ON Canada
    Posts
    1,708
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)
    Are you running this directly in mysql or through php or something?
    If not directly in mysql change your select to just select (*) and leave the rest the same in both cases. How many rows does it return in both cases?

    The order by will not change the number of rows returned. I'm guessing it is something elsewhere in your code.

  3. #3
    SitePoint Evangelist webchalkboard's Avatar
    Join Date
    Jan 2005
    Location
    Bristol, UK
    Posts
    494
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)
    Hi,

    I'm running this in the mysql command line shell.

    If I run

    Code:
    select * from cashback where user_id=1912;
    I get 1037 rows in set (0.01 sec)

    But if I order by the epi_report_id column ie.

    Code:
    select * from cashback where user_id=1912 order by epi_report_id;
    I get 1204 rows in set (0.02 sec)

    Please can someone explain why sorting by a column should effect the number of rows returned...?

    I also noticed that if I count the rows I do get the same result each time, i.e.
    Code:
    mysql> select count(*) as count from cashback where user_id=1912 order by epi_report_id;
    +-------+
    | count |
    +-------+
    |  1037 |
    +-------+
    1 row in set (0.00 sec)
    and

    Code:
    mysql> select count(*) as count from cashback where user_id=1912;
    +-------+
    | count |
    +-------+
    |  1037 |
    +-------+
    1 row in set (0.00 sec)
    Just incase you think the structure of the table could be effecting things here it is:

    Code:
    mysql> describe cashback;
    +---------------+---------------+------+-----+---------+----------------+
    | Field         | Type          | Null | Key | Default | Extra          |
    +---------------+---------------+------+-----+---------+----------------+
    | cashback_id   | int(11)       |      | PRI | NULL    | auto_increment |
    | user_id       | int(11)       |      | MUL | 0       |                |
    | epi_report_id | int(11)       |      | MUL | 0       |                |
    | payout        | float         |      |     | 0       |                |
    | paid          | enum('0','1') |      | MUL | 0       |                |
    | reversed      | enum('0','1') |      | MUL | 0       |                |
    | revdate       | int(11)       |      |     | 0       |                |
    +---------------+---------------+------+-----+---------+----------------+
    7 rows in set (0.00 sec)
    Thanks,
    Tom
    Websites for Sale - Sell websites in a purpose built marketplace
    Then do some Shopping

  4. #4
    SQL Consultant gold trophysilver trophybronze trophy
    r937's Avatar
    Join Date
    Jul 2002
    Location
    Toronto, Canada
    Posts
    39,270
    Mentioned
    60 Post(s)
    Tagged
    3 Thread(s)
    Quote Originally Posted by webchalkboard
    Please can someone explain why sorting by a column should effect the number of rows returned...?
    it shouldn't

    it's friday the 13th

    drop and recreate your index(es), repair your table, and reboot the server

    i don't actually know if that will solve your problem but it couldn't hurt
    rudy.ca | @rudydotca
    Buy my SitePoint book: Simply SQL
    "giving out my real stuffs"

  5. #5
    SitePoint Evangelist webchalkboard's Avatar
    Join Date
    Jan 2005
    Location
    Bristol, UK
    Posts
    494
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)
    Thanks I repaired the table and the results seem to match now!
    Websites for Sale - Sell websites in a purpose built marketplace
    Then do some Shopping


Bookmarks

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •