| |
|
If this is your first visit, be sure to check out the FAQ by clicking the link above.
You may have to register before you can post: click the register link above to proceed.
To start viewing messages, select the forum that you want to visit from the selection below.
|
 |
10-29-08, 04:27
|
|
Registered User
|
|
Join Date: Oct 2008
Posts: 14
|
|
|
sorting on multiple criteria
|
|
I'm trying to sort on multiple criteria. e.g. varchar, varchar, int, bigint. However, I want bigint to be sort from smallest to largest (if case bigint = 0) list those entries below the rest.
illustration:
sitename, siteurl, 4, 2324
sitename, siteurl, 5, 26248
sitename, siteurl, 4, 732456
sitename, siteurl, 6, 932400
sitename, siteurl, 2, 9932412
sitename, siteurl, 5, 23240876
sitename, siteurl, 2, 0
sitename, siteurl, 0, 0
sitename, siteurl, 1, 0
Is this possible? 
|
|
10-29-08, 07:35
|
|
SQL Consultant
|
|
Join Date: Apr 2002
Location: Toronto, Canada
Posts: 19,534
|
|
Code:
ORDER
BY sitename
, siteurl
, someint
, CASE WHEN somebigint = 0 then 937 ELSE 0 END
, somebigint
|
|
10-29-08, 08:31
|
|
Registered User
|
|
Join Date: Oct 2008
Posts: 14
|
|
|
|
Quote:
|
Originally Posted by r937
Code:
ORDER
BY sitename
, siteurl
, someint
, CASE WHEN somebigint = 0 then 937 ELSE 0 END
, somebigint
|
What does the 937 do? Doh! or is that just your handle?
|
|
10-29-08, 09:19
|
|
www.gvee.co.uk
|
|
Join Date: Jan 2007
Location: UK
Posts: 10,156
|
|
It's an abitrary number greater than the previous value used to determine the custom order.
|
|
10-29-08, 09:53
|
|
SQL Consultant
|
|
Join Date: Apr 2002
Location: Toronto, Canada
Posts: 19,534
|
|
it is not "abitrary" 
|
|
10-29-08, 10:09
|
|
Registered User
|
|
Join Date: Oct 2008
Posts: 14
|
|
|
I can't seem to get this to work, maybe its a php issue
$query = "SELECT `SiteName`, `SiteURL`, `PageRank`, `AlexaRank` FROM `SocialSites` WHERE `SiteName` LIKE '%$trimmed%' ORDER BY `AlexaRank`";
$numresults = mysql_query($query);
$numrows=mysql_num_rows($numresults);
...works fine.
no matter how I place: CASE WHEN `AlexaRank` = 0 then 937 ELSE 0 END within the string right before or after the ORDER BY statement I get an error.
|
|
10-29-08, 10:27
|
|
SQL Consultant
|
|
Join Date: Apr 2002
Location: Toronto, Canada
Posts: 19,534
|
|
first of all, please dispense with the unnecessary and annoying backticks
Code:
SELECT SiteName
, SiteURL
, PageRank
, AlexaRank
FROM SocialSites
WHERE SiteName LIKE '%$trimmed%'
ORDER
BY CASE WHEN AlexaRank = 0 THEN 937 ELSE 0 END
, AlexaRank
|
|
10-29-08, 10:47
|
|
Registered User
|
|
Join Date: Oct 2008
Posts: 14
|
|
Quote:
|
Originally Posted by r937
first of all, please dispense with the unnecessary and annoying backticks
Code:
SELECT SiteName
, SiteURL
, PageRank
, AlexaRank
FROM SocialSites
WHERE SiteName LIKE '%$trimmed%'
ORDER
BY CASE WHEN AlexaRank = 0 THEN 937 ELSE 0 END
, AlexaRank
|
Still doesn't seem to work. I'm getting a Warning: mysql_num_rows(): supplied argument is not a valid MySQL result resource error.
Thanks helping out though. I truly appreciate it and am loving db design!
|
|
10-29-08, 11:01
|
|
SQL Consultant
|
|
Join Date: Apr 2002
Location: Toronto, Canada
Posts: 19,534
|
|
that, sir, is not a mysql error, it is a generic php error
run your query outside of php to see whether it works or not, or else turn on proper php error detection
|
|
10-29-08, 11:25
|
|
Registered User
|
|
Join Date: Oct 2008
Posts: 14
|
|
Thanks 937, I got it firing now ...you Rock! I think I'm going to bail for a while, I've been up all night schooling myself. I can't tell you how much I appreciate your help. 
Are you the dbforums master? 
|
|
| Thread Tools |
Search this Thread |
|
|
|
| Display Modes |
 Linear Mode
|
Posting Rules
|
You may not post new threads
You may not post replies
You may not post attachments
You may not edit your posts
HTML code is Off
|
|
|
|
|
dBforums
>
sorting on multiple criteria
