Re: How to optimize ugly order by?
Can you do: SELECT IF(membershiptype = 0, 3, membershiptype) AS s1 FROM... ORDER BY s1 Shouldn't that work? Basically if mtype is 0 return 3 otherwise return the membership type... and sort by that field. lemme know if that works or if I'm dead wrong ;) Henrik Schröder wrote: Hi all, I have a table of users which contains a lot of information, and also a column called membershiptype which can either be 0, 1, or 2. This table is used a lot in various searches, and all results should be ordered by membership type, but the order should not be 0-1-2, but instead 1-2-0. Currently, this is achieved like this: SELECT ... ORDER BY (membershiptype 1) ASC, (membershiptype 2) ASC, login ASC ...which is rather ugly, and forces MySQL to create a temp table with the calculated expressions and then re-sort the result using these. Since this query is used a lot, it would be nice if I could get rid of this. I'm completely stumped. Any ideas? No, I did not choose the sort order. No, I can not change the values used. Yes, it has to be this sort order. :-) /Henrik Schröder -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Selecting non opposites
I have a table of adjectives: ++ | word | ++ | green | | ugly | | dark | | evil | | female | | male | | drunk | ++ and I want to select three of them randomly, but I don't want female and male to accidentally appear together (since they are opposites). I can't think of a query that can do that, although it seems like there should be something easy. Any help out there? Thanks ;) -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: Selecting non opposites
Yeah, I want to avoid code if possible, some ideas was marking adjectives with a class tag, and doing a group by class so only one item in any given class would appera e.g. good and evil would have the same class or male and female would have the same class. this, however, requires me to enter a class id for each thing which is time(or code) consuming, but doable. Diana Cristina Neves Soares wrote: To select random values you could use: SELECT word FROM table ORDER BY rand() LIMIT 3 But the problem of not to select male and female together... Well, you could LIMIT your query to 4 and in your code, if one entry is one of male or female, you test the others not to be female or male (respectively). For shure, you will always have 3 different values and you won't have male and female together (pityfully ;-) Diana Soares -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Query pages by Alphabet
I have a page that is a directory of names ordered by lastname, firstname (e.g. SELECT fn, ln FROM directory ORDER BY ln, fn LIMIT 0,10). I have it paginated so that there are 10 results per page. What I'd like to do instead of having page numbers (which can be unhelpful when trying to page through people's names) is be more like a phone book and let people click on links that are the first letters of their last names For example, let's say my result set for SELECT fn, ln FROM directory ORDER BY ln, fn LIMIT 30, 10 is Jackson Johnson Knutson Kraig Liver Lombard Marx Maxx Milton Nixon The page link would be J-N I know how to get the letters for one page (well I think I do at least), but I want to get them for all pages So basically I'd have something like this for my page list: A B-C D E-G F-H I J-N O-Z and clicking on each page would result in entries only from that letter. The trick is I don't want more than 10 entries a page. Is there an easy way to do this? Possibly in a single query? Thanks -dd -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
