Query not working right

[Nightfire]

I'm trying to get users with the refer field having a value more than 0 to show. That works ok, I'm also trying to arrange this in date order, so if everyone has 1 in their refer field, the person that had the refer field filled in last will show at the top of the list.

select * from cpusers where refer>0 order by refer and refertime desc limit 0,10


I'm trying to get the list to show like this:

Username1 - 2 referrers
Username2 - 1 referrer
Username3 - 1 referrer

Username1 will always be above the users with 1 referrer, Username2 is above Username3 as he/she got the referrer after Username3 did.

Make sense? :confused:

[Spookster]

if you want to sort is you might want to put the ORDER BY refertime DESC in there. AND operator is not what you want there.

[beetle]

If I'm not mistaken, you can separate ORDER BY fields with a comma. They are given precendence in order from left to right...SELECT * FROM cpusers WHERE refer>0 ORDER BY refer, refertime DESC LIMIT 0,10

[Nightfire]

Thanks beetle :) That was the one that did the trick

[Nightfire]

I spoke too soon. The DESC or ASC doesn't seem to work in the query. I'm getting:

Username3 - 1 referrer
Username2 - 1 referrer
Username1 - 3 refferers

Username3 should be at the bottom and Username1 should be at the top, as Username1 has more referrers and Username2 had a referrer after Username3

[beetle]

Are you testing your queries? Use phpMyAdmin or something like DBTools (http://www.dbtools.com.br/EN/). That way you can test your queries standalone without having to stuff them into any PHP, and gives you a good look at the raw data that is returned...

[rcreyes]

I found this in MySql.com, I don't really know if this has something to do with your query:

If you are selecting only a few rows with LIMIT, MySQL will use indexes in some cases when it normally would prefer to do a full table scan.

If you use LIMIT # with ORDER BY, MySQL will end the sorting as soon as it has found the first # lines instead of sorting the whole table.


Thanks
Ray

[Nightfire]

I'm using phpMyAdmin. I've tried everything I can think of, and everything I was given to me to try, nothing seemed to do it, so I'm just using it to list refer in order, instead of refertime too.

SELECT * FROM cpusers WHERE refer>0 ORDER BY refer DESC LIMIT 0,10