order_by.result 16.6 KB
Newer Older
unknown's avatar
unknown committed
1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 17
drop table if exists t1,t2,t3;
CREATE TABLE t1 (
id int(6) DEFAULT '0' NOT NULL,
idservice int(5),
clee char(20) NOT NULL,
flag char(1),
KEY id (id),
PRIMARY KEY (clee)
);
INSERT INTO t1 VALUES (2,4,'6067169d','Y');
INSERT INTO t1 VALUES (2,5,'606716d1','Y');
INSERT INTO t1 VALUES (2,1,'606717c1','Y');
INSERT INTO t1 VALUES (3,1,'6067178d','Y');
INSERT INTO t1 VALUES (2,6,'60671515','Y');
INSERT INTO t1 VALUES (2,7,'60671569','Y');
INSERT INTO t1 VALUES (2,3,'dd','Y');
CREATE TABLE t2 (
18
id int(6) NOT NULL auto_increment,
unknown's avatar
unknown committed
19 20 21 22 23 24 25 26 27 28 29 30 31 32 33 34 35 36
description varchar(40) NOT NULL,
idform varchar(40),
ordre int(6) unsigned DEFAULT '0' NOT NULL,
image varchar(60),
PRIMARY KEY (id),
KEY id (id,ordre)
);
INSERT INTO t2 VALUES (1,'Emettre un appel d''offres','en_construction.html',10,'emettre.gif');
INSERT INTO t2 VALUES (2,'Emettre des soumissions','en_construction.html',20,'emettre.gif');
INSERT INTO t2 VALUES (7,'Liste des t2','t2_liste_form.phtml',51060,'link.gif');
INSERT INTO t2 VALUES (8,'Consulter les soumissions','consulter_soumissions.phtml',200,'link.gif');
INSERT INTO t2 VALUES (9,'Ajouter un type de materiel','typeMateriel_ajoute_form.phtml',51000,'link.gif');
INSERT INTO t2 VALUES (10,'Lister/modifier un type de materiel','typeMateriel_liste_form.phtml',51010,'link.gif');
INSERT INTO t2 VALUES (3,'Crer une fiche de client','clients_ajoute_form.phtml',40000,'link.gif');
INSERT INTO t2 VALUES (4,'Modifier des clients','en_construction.html',40010,'link.gif');
INSERT INTO t2 VALUES (5,'Effacer des clients','en_construction.html',40020,'link.gif');
INSERT INTO t2 VALUES (6,'Ajouter un service','t2_ajoute_form.phtml',51050,'link.gif');
select t1.id,t1.idservice,t2.ordre,t2.description  from t1, t2 where t1.id = 2   and t1.idservice = t2.id  order by t2.ordre;
37 38 39 40 41 42 43
id	idservice	ordre	description
2	1	10	Emettre un appel d'offres
2	3	40000	Crer une fiche de client
2	4	40010	Modifier des clients
2	5	40020	Effacer des clients
2	6	51050	Ajouter un service
2	7	51060	Liste des t2
unknown's avatar
unknown committed
44 45 46 47 48 49
drop table t1,t2;
create table t1 (first char(10),last char(10));
insert into t1 values ("Michael","Widenius");
insert into t1 values ("Allan","Larsson");
insert into t1 values ("David","Axmark");
select concat(first," ",last) as name from t1 order by name;
50 51 52 53
name
Allan Larsson
David Axmark
Michael Widenius
unknown's avatar
unknown committed
54
select concat(last," ",first) as name from t1 order by name;
55 56 57 58
name
Axmark David
Larsson Allan
Widenius Michael
unknown's avatar
unknown committed
59 60 61 62
drop table t1;
create table t1 (i int);
insert into t1 values(1),(2),(1),(2),(1),(2),(3);
select distinct i from t1;
63 64 65 66
i
1
2
3
unknown's avatar
unknown committed
67
select distinct i from t1 order by rand(5);
68 69 70 71
i
1
3
2
unknown's avatar
unknown committed
72
select distinct i from t1 order by i desc;
73 74 75 76
i
3
2
1
unknown's avatar
unknown committed
77
select distinct i from t1 order by 1-i;
78 79 80 81
i
3
2
1
unknown's avatar
unknown committed
82
select distinct i from t1 order by mod(i,2),i;
83 84 85 86
i
2
1
3
unknown's avatar
unknown committed
87 88 89 90
drop table t1;
create table t1 (id int not null,col1 int not null,col2 int not null,index(col1));
insert into t1 values(1,2,2),(2,2,1),(3,1,2),(4,1,1),(5,1,4),(6,2,3),(7,3,1),(8,2,4);
select * from t1 order by col1,col2;
91 92 93 94 95 96 97 98 99
id	col1	col2
4	1	1
3	1	2
5	1	4
2	2	1
1	2	2
6	2	3
8	2	4
7	3	1
unknown's avatar
unknown committed
100
select col1 from t1 order by id;
101 102 103 104 105 106 107 108 109
col1
2
2
1
1
1
2
3
2
unknown's avatar
unknown committed
110
select col1 as id from t1 order by t1.id;
111 112 113 114 115 116 117 118 119
id
1
1
1
2
2
2
2
3
unknown's avatar
unknown committed
120
select concat(col1) as id from t1 order by t1.id;
121 122 123 124 125 126 127 128 129
id
2
2
1
1
1
2
3
2
unknown's avatar
unknown committed
130 131 132 133 134 135 136
drop table t1;
CREATE TABLE t1 (id int auto_increment primary key,aika varchar(40),aikakentta  timestamp);
insert into t1 (aika) values ('Keskiviikko');
insert into t1 (aika) values ('Tiistai');
insert into t1 (aika) values ('Maanantai');
insert into t1 (aika) values ('Sunnuntai');
SELECT FIELD(SUBSTRING(t1.aika,1,2),'Ma','Ti','Ke','To','Pe','La','Su') AS test FROM t1 ORDER by test;
137 138 139 140 141
test
1
2
3
7
unknown's avatar
unknown committed
142 143 144 145 146 147 148 149 150 151 152 153 154 155 156 157 158 159 160 161 162 163 164 165 166 167 168 169 170 171 172 173 174 175
drop table t1;
CREATE TABLE t1
(
a          int unsigned       NOT NULL,
b          int unsigned       NOT NULL,
c          int unsigned       NOT NULL,
UNIQUE(a),
INDEX(b),
INDEX(c)
);
CREATE TABLE t2
(
c          int unsigned       NOT NULL,
i          int unsigned       NOT NULL,
INDEX(c)
);
CREATE TABLE t3
(
c          int unsigned       NOT NULL,
v          varchar(64),
INDEX(c)
);
INSERT INTO t1 VALUES (1,1,1);
INSERT INTO t1 VALUES (2,1,2);
INSERT INTO t1 VALUES (3,2,1);
INSERT INTO t1 VALUES (4,2,2);
INSERT INTO t2 VALUES (1,50);
INSERT INTO t2 VALUES (2,25);
INSERT INTO t3 VALUES (1,'123 Park Place');
INSERT INTO t3 VALUES (2,'453 Boardwalk');
SELECT    a,b,if(b = 1,i,if(b = 2,v,''))
FROM      t1
LEFT JOIN t2 USING(c)
LEFT JOIN t3 ON t3.c = t1.c;
176 177 178 179 180
a	b	if(b = 1,i,if(b = 2,v,''))
1	1	50
2	1	25
3	2	123 Park Place
4	2	453 Boardwalk
unknown's avatar
unknown committed
181 182 183 184 185
SELECT    a,b,if(b = 1,i,if(b = 2,v,''))
FROM      t1
LEFT JOIN t2 USING(c)
LEFT JOIN t3 ON t3.c = t1.c
ORDER BY a;
186 187 188 189 190
a	b	if(b = 1,i,if(b = 2,v,''))
1	1	50
2	1	25
3	2	123 Park Place
4	2	453 Boardwalk
unknown's avatar
unknown committed
191 192 193 194 195 196 197 198
drop table t1,t2,t3;
create table t1 (ID int not null primary key, TransactionID int not null);
insert into t1 (ID, TransactionID) values  (1,  87), (2,  89), (3,  92), (4,  94), (5,  486), (6,  490), (7,  753), (9,  828), (10, 832), (11, 834), (12, 840);
create table t2 (ID int not null primary key, GroupID int not null);
insert into t2 (ID, GroupID) values (87,  87), (89,  89), (92,  92), (94,  94), (486, 486), (490, 490),(753, 753), (828, 828), (832, 832), (834, 834), (840, 840);
create table t3 (ID int not null primary key, DateOfAction date not null);
insert into t3 (ID, DateOfAction) values  (87,  '1999-07-19'), (89,  '1999-07-19'), (92,  '1999-07-19'), (94,  '1999-07-19'), (486, '1999-07-18'), (490, '2000-03-27'), (753, '2000-03-28'), (828, '1999-07-27'), (832, '1999-07-27'),(834, '1999-07-27'), (840, '1999-07-27');
select t3.DateOfAction, t1.TransactionID from t1 join t2 join t3 where t2.ID = t1.TransactionID and t3.ID = t2.GroupID order by t3.DateOfAction, t1.TransactionID;
199 200 201 202 203 204 205 206 207 208 209 210
DateOfAction	TransactionID
1999-07-18	486
1999-07-19	87
1999-07-19	89
1999-07-19	92
1999-07-19	94
1999-07-27	828
1999-07-27	832
1999-07-27	834
1999-07-27	840
2000-03-27	490
2000-03-28	753
unknown's avatar
unknown committed
211
select t3.DateOfAction, t1.TransactionID from t1 join t2 join t3 where t2.ID = t1.TransactionID and t3.ID = t2.GroupID order by t1.TransactionID,t3.DateOfAction;
212 213 214 215 216 217 218 219 220 221 222 223
DateOfAction	TransactionID
1999-07-19	87
1999-07-19	89
1999-07-19	92
1999-07-19	94
1999-07-18	486
2000-03-27	490
2000-03-28	753
1999-07-27	828
1999-07-27	832
1999-07-27	834
1999-07-27	840
unknown's avatar
unknown committed
224 225 226 227 228 229 230 231 232 233 234 235 236 237 238 239 240 241 242 243 244 245 246 247 248 249 250 251 252 253 254 255 256 257 258
drop table t1,t2,t3;
drop table if exists t1;
CREATE TABLE t1 (
member_id int(11) NOT NULL auto_increment,
inschrijf_datum varchar(20) NOT NULL default '',
lastchange_datum varchar(20) NOT NULL default '',
nickname varchar(20) NOT NULL default '',
password varchar(8) NOT NULL default '',
voornaam varchar(30) NOT NULL default '',
tussenvoegsels varchar(10) NOT NULL default '',
achternaam varchar(50) NOT NULL default '',
straat varchar(100) NOT NULL default '',
postcode varchar(10) NOT NULL default '',
wijk varchar(40) NOT NULL default '',
plaats varchar(50) NOT NULL default '',
telefoon varchar(10) NOT NULL default '',
geboortedatum date NOT NULL default '0000-00-00',
geslacht varchar(5) NOT NULL default '',
email varchar(80) NOT NULL default '',
uin varchar(15) NOT NULL default '',
homepage varchar(100) NOT NULL default '',
internet varchar(15) NOT NULL default '',
scherk varchar(30) NOT NULL default '',
favo_boek varchar(50) NOT NULL default '',
favo_tijdschrift varchar(50) NOT NULL default '',
favo_tv varchar(50) NOT NULL default '',
favo_eten varchar(50) NOT NULL default '',
favo_muziek varchar(30) NOT NULL default '',
info text NOT NULL,
ipnr varchar(30) NOT NULL default '',
PRIMARY KEY  (member_id)
) TYPE=MyISAM PACK_KEYS=1;
insert into t1 (member_id) values (1),(2),(3);
select member_id, nickname, voornaam FROM t1
ORDER by lastchange_datum DESC LIMIT 2;
259 260 261
member_id	nickname	voornaam
1		
2		
unknown's avatar
unknown committed
262 263 264 265
drop table t1;
create table t1 (a int not null, b int, c varchar(10), key (a, b, c));
insert into t1 values (1, NULL, NULL), (1, NULL, 'b'), (1, 1, NULL), (1, 1, 'b'), (1, 1, 'b'), (2, 1, 'a'), (2, 1, 'b'), (2, 2, 'a'), (2, 2, 'b'), (2, 3, 'c'),(1,3,'b');
explain select * from t1 where (a = 1 and b is null and c = 'b') or (a > 2) order by a desc;
266
table	type	possible_keys	key	key_len	ref	rows	Extra
267
t1	range	a	a	20	NULL	2	Using where; Using index
unknown's avatar
unknown committed
268
select * from t1 where (a = 1 and b is null and c = 'b') or (a > 2) order by a desc;
269 270
a	b	c
1	NULL	b
unknown's avatar
unknown committed
271
explain select * from t1 where a >= 1 and a < 3 order by a desc;
unknown's avatar
unknown committed
272
table	type	possible_keys	key	key_len	ref	rows	Extra
273
t1	range	a	a	4	NULL	10	Using where; Using index
unknown's avatar
unknown committed
274
select * from t1 where a >= 1 and a < 3 order by a desc;
unknown's avatar
unknown committed
275 276 277 278 279 280 281 282 283 284 285 286
a	b	c
2	3	c
2	2	b
2	2	a
2	1	b
2	1	a
1	3	b
1	1	b
1	1	b
1	1	NULL
1	NULL	b
1	NULL	NULL
unknown's avatar
unknown committed
287
explain select * from t1 where a = 1 order by a desc, b desc;
unknown's avatar
unknown committed
288
table	type	possible_keys	key	key_len	ref	rows	Extra
289
t1	ref	a	a	4	const	5	Using where; Using index
unknown's avatar
unknown committed
290
select * from t1 where a = 1 order by a desc, b desc;
unknown's avatar
unknown committed
291 292
a	b	c
1	3	b
293 294
1	1	b
1	1	b
295
1	1	NULL
unknown's avatar
unknown committed
296
1	NULL	b
297
1	NULL	NULL
unknown's avatar
unknown committed
298
explain select * from t1 where a = 1 and b is null order by a desc, b desc;
unknown's avatar
unknown committed
299
table	type	possible_keys	key	key_len	ref	rows	Extra
300
t1	ref	a	a	9	const,const	2	Using where; Using index; Using filesort
unknown's avatar
unknown committed
301
select * from t1 where a = 1 and b is null order by a desc, b desc;
unknown's avatar
unknown committed
302 303 304
a	b	c
1	NULL	NULL
1	NULL	b
unknown's avatar
unknown committed
305
explain select * from t1 where a >= 1 and a < 3 and b >0 order by a desc,b desc;
unknown's avatar
unknown committed
306
table	type	possible_keys	key	key_len	ref	rows	Extra
unknown's avatar
unknown committed
307
t1	range	a	a	9	NULL	8	Using where; Using index
unknown's avatar
unknown committed
308
explain select * from t1 where a = 2 and b >0 order by a desc,b desc;
unknown's avatar
unknown committed
309
table	type	possible_keys	key	key_len	ref	rows	Extra
310
t1	range	a	a	9	NULL	4	Using where; Using index
unknown's avatar
unknown committed
311
explain select * from t1 where a = 2 and b is null order by a desc,b desc;
unknown's avatar
unknown committed
312
table	type	possible_keys	key	key_len	ref	rows	Extra
313
t1	ref	a	a	9	const,const	1	Using where; Using index; Using filesort
unknown's avatar
unknown committed
314 315
explain select * from t1 where a = 2 and (b is null or b > 0) order by a
desc,b desc;
unknown's avatar
unknown committed
316
table	type	possible_keys	key	key_len	ref	rows	Extra
317
t1	range	a	a	9	NULL	5	Using where; Using index
unknown's avatar
unknown committed
318
explain select * from t1 where a = 2 and b > 0 order by a desc,b desc;
unknown's avatar
unknown committed
319
table	type	possible_keys	key	key_len	ref	rows	Extra
320
t1	range	a	a	9	NULL	4	Using where; Using index
unknown's avatar
unknown committed
321
explain select * from t1 where a = 2 and b < 2 order by a desc,b desc;
unknown's avatar
unknown committed
322
table	type	possible_keys	key	key_len	ref	rows	Extra
unknown's avatar
unknown committed
323 324 325 326 327 328 329 330 331 332 333 334
t1	range	a	a	9	NULL	2	Using where; Using index
explain select * from t1 where a = 1 order by b desc;
table	type	possible_keys	key	key_len	ref	rows	Extra
t1	ref	a	a	4	const	5	Using where; Using index
select * from t1 where a = 1 order by b desc;
a	b	c
1	3	b
1	1	b
1	1	b
1	1	NULL
1	NULL	b
1	NULL	NULL
unknown's avatar
unknown committed
335 336
alter table t1 modify b int not null, modify c varchar(10) not null;
explain select * from t1 order by a, b, c;
unknown's avatar
unknown committed
337 338
table	type	possible_keys	key	key_len	ref	rows	Extra
t1	index	NULL	a	18	NULL	11	Using index
unknown's avatar
unknown committed
339
select * from t1 order by a, b, c;
unknown's avatar
unknown committed
340 341 342 343 344 345 346
a	b	c
1	0	
1	0	b
1	1	
1	1	b
1	1	b
1	3	b
347 348
2	1	a
2	1	b
unknown's avatar
unknown committed
349 350 351
2	2	a
2	2	b
2	3	c
unknown's avatar
unknown committed
352
explain select * from t1 order by a desc, b desc, c desc;
353
table	type	possible_keys	key	key_len	ref	rows	Extra
unknown's avatar
unknown committed
354
t1	index	NULL	a	18	NULL	11	Using index
unknown's avatar
unknown committed
355
select * from t1 order by a desc, b desc, c desc;
356
a	b	c
unknown's avatar
unknown committed
357 358 359
2	3	c
2	2	b
2	2	a
360 361
2	1	b
2	1	a
unknown's avatar
unknown committed
362
1	3	b
363 364
1	1	b
1	1	b
unknown's avatar
unknown committed
365 366 367
1	1	
1	0	b
1	0	
unknown's avatar
unknown committed
368
explain select * from t1 where (a = 1 and b = 1 and c = 'b') or (a > 2) order by a desc;
369
table	type	possible_keys	key	key_len	ref	rows	Extra
370
t1	range	a	a	18	NULL	3	Using where; Using index
unknown's avatar
unknown committed
371
select * from t1 where (a = 1 and b = 1 and c = 'b') or (a > 2) order by a desc;
372 373 374
a	b	c
1	1	b
1	1	b
unknown's avatar
unknown committed
375
explain select * from t1 where a < 2 and b <= 1 order by a desc, b desc;
376
table	type	possible_keys	key	key_len	ref	rows	Extra
377
t1	range	a	a	4	NULL	6	Using where; Using index
unknown's avatar
unknown committed
378
select * from t1 where a < 2 and b <= 1 order by a desc, b desc;
379 380 381
a	b	c
1	1	b
1	1	b
unknown's avatar
unknown committed
382 383 384
1	1	
1	0	b
1	0	
unknown's avatar
unknown committed
385
select count(*) from t1 where a < 5 and b > 0;
unknown's avatar
unknown committed
386 387
count(*)
9
unknown's avatar
unknown committed
388
select * from t1 where a < 5 and b > 0 order by a desc,b desc;
unknown's avatar
unknown committed
389 390 391 392 393 394 395 396 397 398
a	b	c
2	3	c
2	2	b
2	2	a
2	1	b
2	1	a
1	3	b
1	1	b
1	1	b
1	1	
unknown's avatar
unknown committed
399
explain select * from t1 where a between 1 and 3 and b <= 1 order by a desc, b desc;
400
table	type	possible_keys	key	key_len	ref	rows	Extra
401
t1	range	a	a	8	NULL	10	Using where; Using index
unknown's avatar
unknown committed
402
select * from t1 where a between 1 and 3 and b <= 1 order by a desc, b desc;
403 404 405 406 407
a	b	c
2	1	b
2	1	a
1	1	b
1	1	b
unknown's avatar
unknown committed
408 409 410
1	1	
1	0	b
1	0	
unknown's avatar
unknown committed
411
explain select * from t1 where a between 0 and 1 order by a desc, b desc;
412
table	type	possible_keys	key	key_len	ref	rows	Extra
413
t1	range	a	a	4	NULL	5	Using where; Using index
unknown's avatar
unknown committed
414
select * from t1 where a between 0 and 1 order by a desc, b desc;
415
a	b	c
unknown's avatar
unknown committed
416
1	3	b
417 418
1	1	b
1	1	b
unknown's avatar
unknown committed
419 420 421
1	1	
1	0	b
1	0	
unknown's avatar
unknown committed
422 423 424 425 426 427 428 429 430 431 432 433 434 435 436 437 438 439 440 441 442 443 444 445 446
drop table t1;
CREATE TABLE t1 (
gid int(10) unsigned NOT NULL auto_increment,
cid smallint(5) unsigned NOT NULL default '0',
PRIMARY KEY  (gid),
KEY component_id (cid)
) TYPE=MyISAM;
INSERT INTO t1 VALUES (103853,108),(103867,108),(103962,108),(104505,108),(104619,108),(104620,108);
ALTER TABLE t1 add skr int(10) not null;
CREATE TABLE t2 (
gid int(10) unsigned NOT NULL default '0',
uid smallint(5) unsigned NOT NULL default '1',
sid tinyint(3) unsigned NOT NULL default '1',
PRIMARY KEY  (gid),
KEY uid (uid),
KEY status_id (sid)
) TYPE=MyISAM;
INSERT INTO t2 VALUES (103853,250,5),(103867,27,5),(103962,27,5),(104505,117,5),(104619,75,5),(104620,15,5);
CREATE TABLE t3 (
uid smallint(6) NOT NULL auto_increment,
PRIMARY KEY  (uid)
) TYPE=MyISAM;
INSERT INTO t3 VALUES (1),(15),(27),(75),(117),(250);
ALTER TABLE t3 add skr int(10) not null;
select t1.gid, t2.sid, t3.uid from t2, t1, t3 where t2.gid = t1.gid and t2.uid = t3.uid order by t3.uid, t1.gid;
447 448 449 450 451 452 453
gid	sid	uid
104620	5	15
103867	5	27
103962	5	27
104619	5	75
104505	5	117
103853	5	250
unknown's avatar
unknown committed
454
select t1.gid, t2.sid, t3.uid from t3, t2, t1 where t2.gid = t1.gid and t2.uid = t3.uid order by t3.uid, t1.gid;
455 456 457 458 459 460 461
gid	sid	uid
104620	5	15
103867	5	27
103962	5	27
104619	5	75
104505	5	117
103853	5	250
unknown's avatar
unknown committed
462
EXPLAIN select t1.gid, t2.sid, t3.uid from t3, t2, t1 where t2.gid = t1.gid and t2.uid = t3.uid order by t1.gid, t3.uid;
463
table	type	possible_keys	key	key_len	ref	rows	Extra
464 465 466
t1	index	PRIMARY	PRIMARY	4	NULL	6	Using index
t2	eq_ref	PRIMARY,uid	PRIMARY	4	t1.gid	1	
t3	eq_ref	PRIMARY	PRIMARY	2	t2.uid	1	Using where; Using index
unknown's avatar
unknown committed
467
EXPLAIN SELECT t1.gid, t3.uid from t1, t3 where t1.gid = t3.uid order by t1.gid,t3.skr;
468
table	type	possible_keys	key	key_len	ref	rows	Extra
469 470
t3	ALL	PRIMARY	NULL	NULL	NULL	6	Using temporary; Using filesort
t1	eq_ref	PRIMARY	PRIMARY	4	t3.uid	1	Using where; Using index
unknown's avatar
unknown committed
471
EXPLAIN SELECT t1.gid, t2.sid, t3.uid from t2, t1, t3 where t2.gid = t1.gid and t2.uid = t3.uid order by t3.uid, t1.gid;
472 473 474
table	type	possible_keys	key	key_len	ref	rows	Extra
t1	index	PRIMARY	PRIMARY	4	NULL	6	Using index; Using temporary; Using filesort
t2	eq_ref	PRIMARY,uid	PRIMARY	4	t1.gid	1	
475
t3	eq_ref	PRIMARY	PRIMARY	2	t2.uid	1	Using where; Using index
unknown's avatar
unknown committed
476
EXPLAIN SELECT t1.gid, t3.uid from t1, t3 where t1.gid = t3.uid order by t3.skr,t1.gid;
477
table	type	possible_keys	key	key_len	ref	rows	Extra
478 479
t3	ALL	PRIMARY	NULL	NULL	NULL	6	Using temporary; Using filesort
t1	eq_ref	PRIMARY	PRIMARY	4	t3.uid	1	Using where; Using index
unknown's avatar
unknown committed
480
EXPLAIN SELECT t1.gid, t3.uid from t1, t3 where t1.skr = t3.uid order by t1.gid,t3.skr;
481 482
table	type	possible_keys	key	key_len	ref	rows	Extra
t1	ALL	NULL	NULL	NULL	NULL	6	Using temporary; Using filesort
483
t3	eq_ref	PRIMARY	PRIMARY	2	t1.skr	1	Using where
unknown's avatar
unknown committed
484
drop table t1,t2,t3;
485 486 487 488 489 490 491 492 493 494 495 496 497 498 499 500 501 502 503 504 505 506 507 508 509 510 511 512 513 514 515 516 517 518 519
CREATE TABLE t1 (
`titre` char(80) NOT NULL default '',
`numeropost` mediumint(8) unsigned NOT NULL auto_increment,
`date` datetime NOT NULL default '0000-00-00 00:00:00',
`auteur` char(35) NOT NULL default '',
`icone` tinyint(2) unsigned NOT NULL default '0',
`lastauteur` char(35) NOT NULL default '',
`nbrep` smallint(6) unsigned NOT NULL default '0',
`dest` char(35) NOT NULL default '',
`lu` tinyint(1) unsigned NOT NULL default '0',
`vue` mediumint(8) unsigned NOT NULL default '0',
`ludest` tinyint(1) unsigned NOT NULL default '0',
`ouvert` tinyint(1) unsigned NOT NULL default '1',
PRIMARY KEY  (`numeropost`),
KEY `date` (`date`),
KEY `dest` (`dest`,`ludest`),
KEY `auteur` (`auteur`,`lu`),
KEY `auteur_2` (`auteur`,`date`),
KEY `dest_2` (`dest`,`date`)
) CHECKSUM=1;
CREATE TABLE t2 (
`numeropost` mediumint(8) unsigned NOT NULL default '0',
`pseudo` char(35) NOT NULL default '',
PRIMARY KEY  (`numeropost`,`pseudo`),
KEY `pseudo` (`pseudo`)
);
INSERT INTO t1 (titre,auteur,dest) VALUES ('test','joce','bug');
INSERT INTO t2 (numeropost,pseudo) VALUES (1,'joce'),(1,'bug');
SELECT titre,t1.numeropost,auteur,icone,nbrep,0,date,vue,ouvert,lastauteur,dest FROM t2 LEFT JOIN t1 USING(numeropost) WHERE t2.pseudo='joce' ORDER BY date DESC LIMIT 0,30;
titre	numeropost	auteur	icone	nbrep	0	date	vue	ouvert	lastauteur	dest
test	1	joce	0	0	0	0000-00-00 00:00:00	0	1		bug
SELECT titre,t1.numeropost,auteur,icone,nbrep,'0',date,vue,ouvert,lastauteur,dest FROM t2 LEFT JOIN t1 USING(numeropost) WHERE t2.pseudo='joce' ORDER BY date DESC LIMIT 0,30;
titre	numeropost	auteur	icone	nbrep	0	date	vue	ouvert	lastauteur	dest
test	1	joce	0	0	0	0000-00-00 00:00:00	0	1		bug
drop table t1,t2;
unknown's avatar
unknown committed
520 521 522 523 524 525 526 527 528 529 530 531 532 533 534 535 536 537 538 539 540 541 542 543 544
CREATE TABLE t1 (a int, b int);
INSERT INTO t1 VALUES (1, 2);
INSERT INTO t1 VALUES (3, 4);
INSERT INTO t1 VALUES (5, NULL);
SELECT * FROM t1 ORDER BY b;
a	b
5	NULL
1	2
3	4
SELECT * FROM t1 ORDER BY b DESC;
a	b
3	4
1	2
5	NULL
SELECT * FROM t1 ORDER BY (a + b);
a	b
5	NULL
1	2
3	4
SELECT * FROM t1 ORDER BY (a + b) DESC;
a	b
3	4
1	2
5	NULL
DROP TABLE t1;