quick question on Order BY

[chiefrokka]

I have a field called "Week_Lost".  by default it's 0 until they lose, then it's set to the week they lost.

 

I want to list all the users based on this Week_Lost column... starting from those who haven't lost yet go at the top, (those with values of 0 or Order ASC)... then i want to display those who lasted the longest (Order By Week_Lost DESC)

 

can you do this or do I have to create another column just to track who has lost or not?

that make sense?  lol

[AndyB]

do I have to create another column just to track who has lost or not?

 

No, if Week_Lost equals zero they have not lost, otherwise they have lost.

[shocker-z]

0 should come before all alpha charactors and you would set it as ASC instead of DESC as you want to start low and count up.

 

If not workign then yes you may need 2 wueries to select all with value 0 then run a loop DESC from there on.

 

Regards

Liam

[chiefrokka]

their all numerical values. 

user1 = 0

user2 = 0

user3 = 4

user4 = 3

 

if I do Order By Week_Lost Asc then all my 0's would come first like I want, then user4 would come next though.  after the 0's i want it to DESC

 

 

[shocker-z]

Think you'll have to do a loop through 0 data then loop through the DESC and WHERE column != 0

 

Regards

Liam

[AndyB]

That's two separate queries. Simple enough. One where week_lost =0, the other where week_lost!=0 DESC ORDER by week_lost #

[chiefrokka]

I tried this but it didn't work.  this displays all the 0's first, but then 3, 4 are next so it's not doing the DESC like I want.  If I take off the first DESC then it displays the 3, 4, then 0's..  weird.

<?php
$result = mysql_query("SELECT * FROM Users WHERE LeagueID = '$LeagueID' 
ORDER BY (Week_Lost = 0) DESC, (Week_Lost != 0) DESC  ") 
while ($row = mysql_fetch_array($result)) // loop through every user in the league
{ 
}
?>

 

I have tons of code after I do the while records exists, so not sure how doing 2 queries would work because I use $row['fields'] a lot in the code.  plus I'm using the WHERE for my LeagueID

 

 

[corbin]

There's probably a better way, but worst case you could do:

 

SELECT * FROM users WHERE LeagueID = '{$LeagueID}' AND Week_Lost = 0
UNION
SELECT * FROM Users WHERE LeagueID = '{$LeagueID}' AND Week_Lost <> 0 ORDER BY Week_Lost DESC

[chiefrokka]

There's probably a better way, but worst case you could do:

 

SELECT * FROM users WHERE LeagueID = '{$LeagueID}' AND Week_Lost = 0
UNION
SELECT * FROM Users WHERE LeagueID = '{$LeagueID}' AND Week_Lost <> 0 ORDER BY Week_Lost DESC

 

anybody have an easier way?  if not, can you put this into syntax how I would use it based on my code above.  I'm confused a little... would I do two $Results = or all in one?