Problems with queries

Questions about the MySQL, PostgreSQL, and most other databases, as well as using it with PHP can be asked here.

Moderator: General Moderators

User avatar
aceconcepts
DevNet Resident
Posts: 1424
Joined: Mon Feb 06, 2006 11:26 am
Location: London

Re: Problems with queries

Post by aceconcepts »

VladSun's query should work perfectly for you.
Emmash
Forum Newbie
Posts: 13
Joined: Wed Oct 08, 2008 10:49 am

Re: Problems with queries

Post by Emmash »

I found the solution by reading in MYSQL Documentation.... Here is the solution :

SELECT no_document,titre_document FROM document WHERE no_document=3 OR no_document=1 OR no_document=2 ORDER BY FIELD(no_document,'3','1','2')
Emmash
Forum Newbie
Posts: 13
Joined: Wed Oct 08, 2008 10:49 am

Re: Problems with queries

Post by Emmash »

I found the solution by reading in MYSQL Documentation.... Here is the solution :

Code: Select all

SELECT no_document,titre_document FROM document WHERE no_document=3 OR no_document=1 OR no_document=2 ORDER BY FIELD(no_document,'3','1','2')
User avatar
aceconcepts
DevNet Resident
Posts: 1424
Joined: Mon Feb 06, 2006 11:26 am
Location: London

Re: Problems with queries

Post by aceconcepts »

Fundamentally the same as VladSun's suggestion.

Good research :wink:
User avatar
VladSun
DevNet Master
Posts: 4313
Joined: Wed Jun 27, 2007 9:44 am
Location: Sofia, Bulgaria

Re: Problems with queries

Post by VladSun »

aceconcepts wrote:Fundamentally the same as VladSun's suggestion.

Good research :wink:
Not exactly - it's string comparison, so it would (IMHO) be times slower than a simple int comparison.

PS: What exaclty didn't work with my query? Show us the code, please.
There are 10 types of people in this world, those who understand binary and those who don't
Emmash
Forum Newbie
Posts: 13
Joined: Wed Oct 08, 2008 10:49 am

Re: Problems with queries

Post by Emmash »

I found the solution by reading in MYSQL Documentation.... Here is the solution :

Code: Select all

SELECT no_document,titre_document FROM document WHERE no_document=3 OR no_document=1 OR no_document=2 ORDER BY FIELD(no_document,'3','1','2')
Emmash
Forum Newbie
Posts: 13
Joined: Wed Oct 08, 2008 10:49 am

Re: Problems with queries

Post by Emmash »

VladSun wrote:
aceconcepts wrote:Fundamentally the same as VladSun's suggestion.

Good research :wink:
Not exactly - it's string comparison, so it would (IMHO) be times slower than a simple int comparison.

PS: What exaclty didn't work with my query? Show us the code, please.
I try exactly the code Vlad and I was like the clause "ORDER BY" was not considered because it still shows document 1,document 2, document 3 instead of something like document 3, document 1, document 2

Anyway...I find the solution with the clause FIELD
User avatar
VladSun
DevNet Master
Posts: 4313
Joined: Wed Jun 27, 2007 9:44 am
Location: Sofia, Bulgaria

Re: Problems with queries

Post by VladSun »

mysql> describe test;
+-------+-------------+------+-----+---------+-------+
| Field | Type | Null | Key | Default | Extra |
+-------+-------------+------+-----+---------+-------+
| c | varchar(50) | NO | | 0 | |
| a | int(11) | YES | | NULL | |
+-------+-------------+------+-----+---------+-------+
2 rows in set (0.03 sec)

mysql> SELECT
-> *
-> FROM
-> test
-> WHERE
-> a IN (1, 2, 3)
-> ORDER BY
-> a=3, a=1, a=2
->
-> ;
+-----+------+
| c | a |
+-----+------+
| ss | 2 |
| aa | 2 |
| bbb | 2 |
| v | 1 |
| ff | 1 |
| gg | 3 |
+-----+------+
6 rows in set (0.00 sec)

mysql> select * from test;
+---------+------+
| c | a |
+---------+------+
| v | 1 |
| ss | 2 |
| ff | 1 |
| aa | 2 |
| gg | 3 |
| bbb | 2 |
| ddddddd | 4 |
+---------+------+
7 rows in set (0.00 sec)

mysql>
so as I suggested, you should use DESC:
mysql> SELECT
-> *
-> FROM
-> test
-> WHERE
-> a IN (1, 2, 3)
-> ORDER BY
-> a=3 DESC, a=1 DESC, a=2 DESC
-> ;
+-----+------+
| c | a |
+-----+------+
| gg | 3 |
| v | 1 |
| ff | 1 |
| ss | 2 |
| aa | 2 |
| bbb | 2 |
+-----+------+
6 rows in set (0.00 sec)

mysql>
There are 10 types of people in this world, those who understand binary and those who don't
Post Reply