mySQL ORDER BY problem

need to sort first a given user, then the rest

[mcibor]

Hi!
I have a problem with building a mySQL query.
I have a table with:

id name state
1 Jake available
2 Jake unavailable
3 Ann available
4 Ann unavailable
5 Zak available

Suppose I'm user = Jake. I want to select all records that are mine or available:
SELECT name FROM tbl WHERE name='Jake' AND state='available';

However I would like to place them not in random order, but user's records first, and then if possible other sorted by creation.

To select by creation is easy - just ORDER BY id DESC, but how to order so, that Jake is first?

The output should be:
1,2,5,3 (- id)

Best regards
Michal Cibor

PS. A question concerning this forum: How do you quote stuff? I mean parts of previous messages with grey background and a border?
Thanks!

[Salsa]

(SELECT * FROM table WHERE name='Jake' ORDER BY creation) UNION (SELECT * FROM table WHERE state = 'available' ORDER BY creation);

[helenp]

I am not sure if an union is what you want,
if so,
I think, only think the union above is not correct,
should be:
(SELECT whatever FROM table, WHERE whatever = whatever
UNION ALL
(SELECT whatever FROM table WHERE whatever = whatever )
ORDER BY whatever, another_whatever

Maybe what you need is to do:
SELECT name FROM tbl WHERE name='Jake' OR state='available' ORDER BY name, state;

[StupidScript]

mcibor, click the link for "help" (near the upper-left corner) and check "HTML Style Codes". While you're testing your "bbcode" development, you should use the "Preview" button before submitting your post, just to be sure.

(BTW: Welcome to the forums! :)

[Salsa]

helenp,

Your first suggestion is valid, but I did a (rare) test before posting, using simply UNION on one of my existing tables, and it did produce the results that mcibor wants. When using UNION on two different tables, however, it might not, but on on one table it does.

Also, your suggestion,

SELECT name FROM tbl WHERE name='Jake' OR state='available' ORDER BY name, state;

...will not put 'Jake' at the top as mcibor wishes.

I wish you well,
Salsa

[Salsa]

Actually, helenp, I take that back. Even your first suggestion is not valid (missing closing parenthesis aside). If the ORDER BYs are not included in the individual SELECT queries, the results will not be in the required order. That was my whole purpose of using the UNION. (And if only one ORDER BY is placed at the end, the parentheses are not needed at all.)

To the powers that be: I tried to edit my last post, but the "Owner Edit" link was not available. (This complaint is probably for naught, however, as I'll bet that all of the administrators are at PubCon:)!

Edit: This is just to affirm that my "Owner Edit" is back again. I just wish I hadn't brought up PubCon to myself, becauseI sure wish I were there! I'm still kicking myself for missing the Orlando one because it was practically out my back door.

[coopster]

Let me join StupidScript in welcoming you to WebmasterWorld, mcibor.

A UNION [dev.mysql.com] is a great solution to this issue, you've got a push in the right direction, now you'll have to tweak the statement to meet your needs. Keep in mind that UNION became available in MySQL >= 4.0. Prior to that you can resolve the issue using a temporary table [dev.mysql.com].

The Owner Edit link is on a timed delay, disappearing after a period of time that has been set by the WebmasterWorld Forums owner.

[mcibor]

Thanks Salsa for the answer. It works perfectly! Even with LIMIT there is no problem.
Question now looks like below:
(SELECT * FROM table WHERE name='Jake' ORDER BY id DESC) UNION (SELECT * FROM table WHERE state = 'available' ORDER BY id DESC) LIMIT x,y;
where x is the beginning record and y is how many to show.
I use limit as google to show only eg. 20 out of 200 records.
I couldn't use ORDER BY CREATION, because creation is not a mySQL command. No problem.

Sorry for making a mistake in WHERE statement. Certainly it should be OR not AND. Hope it didn't cause any problems.

Best wishes all!
Micha³ Cibor

[mcibor]

Thanks StupidScript for info. I've read [b]all the stuff[b] in [i]help[i] but I can't see the codes in preview button (I haven't disabled anything). And still I don't know how to make a quote like in [webmasterworld.com...] message sent by pete_m.

Best regards
Micha³ Cibor

[Salsa]

Michal: I'm glad it's working for you. Sorry about the "ORDER BY creation" confusion. I was just being schematic and meant only ORDER BY whatever, and you'd used creation a couple of times in your initial post. As for the AND/OR thing, I hadn't even noticed it because you made clear the results that you wanted elsewhere.

Coopster: Thanks for the Owner Edit explanation. I see now that no older posts have the Owner Edit option. I guess I'd never tried to edit a post more than minutes old before and hadn't noticed that the option disappeard after a bit. The time delay is a good scheme to preserve continuity.

[StupidScript]

mcibor:

[b]all the stuff[b] in [i]help[i]

You're missing the "slash" in the closing BBtag.

[ b ]bold[ /b ] (I used extra spaces so you can see)
[ quote ]Quoted stuff ...[ /quote ]

Just like HTML except using square brackets instead of angle brackets. If you have proper opening and closing tags, you'll see the result in "Preview" before submitting. :)

[mcibor]

Thanks a lot.
Yes! Now it works fine.

Merry Christmas Everyone!