SitePoint Sponsor |
|
User Tag List
Results 1 to 5 of 5
-
Jan 27, 2006, 06:19 #1
- 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
However if I sort by a different column
Code:SELECT * FROM `cashback` WHERE `user_id` =1912 ORDER BY `epi_report_id` ASC
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
-
Jan 27, 2006, 08:00 #2
- 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.
-
Jan 27, 2006, 08:14 #3
- 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;
But if I order by the epi_report_id column ie.
Code:select * from cashback where user_id=1912 order by epi_report_id;
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)
Code:mysql> select count(*) as count from cashback where user_id=1912; +-------+ | count | +-------+ | 1037 | +-------+ 1 row in set (0.00 sec)
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)
Tom
-
Jan 27, 2006, 10:18 #4
- Join Date
- Jul 2002
- Location
- Toronto, Canada
- Posts
- 39,347
- Mentioned
- 63 Post(s)
- Tagged
- 3 Thread(s)
Originally Posted by webchalkboard
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
-
Jan 30, 2006, 10:31 #5
- 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!
Bookmarks