But there are cases when.

Demiel: Danblack: http://pastebin.com/h3DA8fdW

Hoecker: I am not sure how to ask this. I am trying to give an “alias” to a computed value in my SELECT. for example LENGTHfield AS length. so that I can use this length in my WHERE clause later

Besco: Is this sort of thing possible

Reph: My query now looks like this: http://pastie.org/10418473

Borba: Djam90: aliases established in the SELECT clause are not available until the resultset is returned can be used in GROUP BY, ORDER BY and HAVING clauses. Aliases established in the FROM clause are available earlier can be used in the WHERE clause.

Rumery: Djam90, you can repeat the expression in WHERE to get the same effect

Mccoid: Hmm Rumery it is just, when you have many things like SUM and LENGTH and similar, and then you need it in the SELECT and the WHERE, lots of duplicated code

Rumery: Djam90, yes, but to be able to use the alias in where you would need to wrap it all in another query, thats usually even worse for readability. but that is for “normal” expressions – for sum you cannot use WHERE anyway, thats what HAVING is for

Bawden: Each user have friends also from users table

Jorski: Users have column last_seen_at

Barre: If last_seen_at is 1 minute ago, user is online

Rahall: Now I want to get list of user friends, which should begin with friends online, sorted by name

Hautamaki: And after firends online the rest friends, also sorted by name

Saine: I can do this in one query with UNION, but I have problem with sorting results by name, because ORDER BY doesn’t work with UNION

Gardenas: Is it possible to do this in one query?

Clyatt: Toomus: ORDER BY works with UNION, but you don’t need UNION. You need join

Rumery: Toomus, you want to order by “name”, but only after ordering by “online”

Spinks: Rumery: you mean ORDER BY two columns? First last_seen_at DESC, and then name ASC?

Demiel: I’m having a tough time getting my mysql server to start

Demiel: I’ve tried increasing innodb_force_recovery to 4, but it still won’t start

Rumery: Toomus, using simply “last_seen_at” would order everything by that time and only those with the same time by name – but you can order by your expression to “decide” who is online – boolean expressions return 0/1 as false/true so can be ordered by

Prince: Rumery: Yeah, this doesn’t work http://pastie.org/10418650

Rumery: Toomus, yes, because it orders them ALL by the “last click”

Rumery: Toomus, how do you compute the “is online” state? in sql or in app code?

Rumery: For this to work you need to compute that in sql

Rumery: Toomus, and as salle wrote, you probably want to add a join between user and friends

Moranda: Rumery: list of friends is holded in user column friend_ids as serialized array

Rumery: Toomus, ah : can you change that to a separate table?

Biase: Rumery: I can’t : But this solution is pretty fast

Rumery: But it is not a problem for the ordering itself, only a thing which should be generally avoided

Dicello: Rumery: I’m using Ruby on Rails framework

Halfhill: Rumery: So it is easy to write/read serialized array from DB

Rumery: Toomus, RoR has nothing to do with that design unless it is THAT bad

Arcea: Toomus: http://gtowey.blogspot.com/2009/12/how-to-fix-comma-separated-list-of-doom.html

Rumery: Toomus, serialized array is only a different version of this

Rumery: Toomus, imagine facebook with millions of users and average hundred of friends per user – just searching them your way would be painfully slow

Rumery: Serialized array has a few drawbacks – cannot be indexed to search fast, no easy way to do a query “did this person already befriended that one” etc

Rumery: But there are cases when you just cannot change that or not at the moment, it happens. so unless you can, we should return to the ordering problem : just keep these for later