order_by.result 25.4 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
drop table t1;
unknown's avatar
unknown committed
88 89 90 91 92 93
create table t1 ( pk     int primary key, name   varchar(255) not null, number varchar(255) not null);
insert into t1 values (1, 'Gamma',     '123'), (2, 'Gamma Ext', '123a'), (3, 'Alpha',     '001'), (4, 'Beta',      '200c');
select distinct t1.name as 'Building Name',t1.number as 'Building Number' from t1 order by t1.name asc;
Building Name	Building Number
Alpha	001
Beta	200c
94 95
Gamma	123
Gamma Ext	123a
unknown's avatar
unknown committed
96
drop table t1;
unknown's avatar
unknown committed
97 98 99
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;
100 101 102 103 104 105 106 107 108
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
109
select col1 from t1 order by id;
110 111 112 113 114 115 116 117 118
col1
2
2
1
1
1
2
3
2
119
select col1 as id from t1 order by id;
120 121 122 123 124 125 126 127 128
id
1
1
1
2
2
2
2
3
129
select concat(col1) as id from t1 order by id;
130 131 132 133 134 135
id
1
1
1
2
2
136 137 138
2
2
3
unknown's avatar
unknown committed
139 140 141 142 143 144 145
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;
146 147 148 149 150
test
1
2
3
7
unknown's avatar
unknown committed
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 176 177 178 179 180 181 182 183 184
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;
185 186 187 188 189
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
190 191 192 193 194
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;
195 196 197 198 199
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
200 201 202 203 204 205 206 207
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;
208 209 210 211 212 213 214 215 216 217 218 219
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
220
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;
221 222 223 224 225 226 227 228 229 230 231 232
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
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 259 260 261 262
drop table t1,t2,t3;
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)
unknown's avatar
unknown committed
263
) ENGINE=MyISAM PACK_KEYS=1;
unknown's avatar
unknown committed
264 265 266
insert into t1 (member_id) values (1),(2),(3);
select member_id, nickname, voornaam FROM t1
ORDER by lastchange_datum DESC LIMIT 2;
267 268 269
member_id	nickname	voornaam
1		
2		
unknown's avatar
unknown committed
270 271 272 273
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;
unknown's avatar
unknown committed
274
id	select_type	table	type	possible_keys	key	key_len	ref	rows	Extra
275
1	SIMPLE	t1	index	a	a	22	NULL	11	Using where; Using index
unknown's avatar
unknown committed
276
select * from t1 where (a = 1 and b is null and c = 'b') or (a > 2) order by a desc;
277 278
a	b	c
1	NULL	b
unknown's avatar
unknown committed
279
explain select * from t1 where a >= 1 and a < 3 order by a desc;
unknown's avatar
unknown committed
280
id	select_type	table	type	possible_keys	key	key_len	ref	rows	Extra
unknown's avatar
unknown committed
281
1	SIMPLE	t1	range	a	a	4	NULL	10	Using where; Using index
unknown's avatar
unknown committed
282
select * from t1 where a >= 1 and a < 3 order by a desc;
unknown's avatar
unknown committed
283 284 285 286 287 288 289 290 291 292 293 294
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
295
explain select * from t1 where a = 1 order by a desc, b desc;
unknown's avatar
unknown committed
296
id	select_type	table	type	possible_keys	key	key_len	ref	rows	Extra
unknown's avatar
unknown committed
297
1	SIMPLE	t1	ref	a	a	4	const	5	Using where; Using index
unknown's avatar
unknown committed
298
select * from t1 where a = 1 order by a desc, b desc;
unknown's avatar
unknown committed
299 300
a	b	c
1	3	b
301 302
1	1	b
1	1	b
303
1	1	NULL
unknown's avatar
unknown committed
304
1	NULL	b
305
1	NULL	NULL
unknown's avatar
unknown committed
306
explain select * from t1 where a = 1 and b is null order by a desc, b desc;
unknown's avatar
unknown committed
307
id	select_type	table	type	possible_keys	key	key_len	ref	rows	Extra
unknown's avatar
unknown committed
308
1	SIMPLE	t1	ref	a	a	9	const,const	2	Using where; Using index; Using filesort
unknown's avatar
unknown committed
309
select * from t1 where a = 1 and b is null order by a desc, b desc;
unknown's avatar
unknown committed
310 311 312
a	b	c
1	NULL	NULL
1	NULL	b
unknown's avatar
unknown committed
313
explain select * from t1 where a >= 1 and a < 3 and b >0 order by a desc,b desc;
unknown's avatar
unknown committed
314
id	select_type	table	type	possible_keys	key	key_len	ref	rows	Extra
315
1	SIMPLE	t1	range	a	a	9	NULL	8	Using where; Using index
unknown's avatar
unknown committed
316
explain select * from t1 where a = 2 and b >0 order by a desc,b desc;
unknown's avatar
unknown committed
317
id	select_type	table	type	possible_keys	key	key_len	ref	rows	Extra
unknown's avatar
unknown committed
318
1	SIMPLE	t1	range	a	a	9	NULL	5	Using where; Using index
unknown's avatar
unknown committed
319
explain select * from t1 where a = 2 and b is null order by a desc,b desc;
unknown's avatar
unknown committed
320
id	select_type	table	type	possible_keys	key	key_len	ref	rows	Extra
unknown's avatar
unknown committed
321
1	SIMPLE	t1	ref	a	a	9	const,const	1	Using where; Using index; Using filesort
unknown's avatar
unknown committed
322 323
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
324
id	select_type	table	type	possible_keys	key	key_len	ref	rows	Extra
unknown's avatar
unknown committed
325
1	SIMPLE	t1	range	a	a	9	NULL	6	Using where; Using index
unknown's avatar
unknown committed
326
explain select * from t1 where a = 2 and b > 0 order by a desc,b desc;
unknown's avatar
unknown committed
327
id	select_type	table	type	possible_keys	key	key_len	ref	rows	Extra
unknown's avatar
unknown committed
328
1	SIMPLE	t1	range	a	a	9	NULL	5	Using where; Using index
unknown's avatar
unknown committed
329
explain select * from t1 where a = 2 and b < 2 order by a desc,b desc;
unknown's avatar
unknown committed
330
id	select_type	table	type	possible_keys	key	key_len	ref	rows	Extra
331
1	SIMPLE	t1	range	a	a	9	NULL	2	Using where; Using index
unknown's avatar
unknown committed
332 333 334
explain select * from t1 where a = 1 order by b desc;
id	select_type	table	type	possible_keys	key	key_len	ref	rows	Extra
1	SIMPLE	t1	ref	a	a	4	const	5	Using where; Using index
unknown's avatar
unknown committed
335 336 337 338 339 340 341 342
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
343
alter table t1 modify b int not null, modify c varchar(10) not null;
unknown's avatar
unknown committed
344
Warnings:
unknown's avatar
unknown committed
345 346 347 348
Warning	1265	Data truncated for column 'b' at row 1
Warning	1265	Data truncated for column 'c' at row 1
Warning	1265	Data truncated for column 'b' at row 2
Warning	1265	Data truncated for column 'c' at row 3
unknown's avatar
unknown committed
349
explain select * from t1 order by a, b, c;
unknown's avatar
unknown committed
350
id	select_type	table	type	possible_keys	key	key_len	ref	rows	Extra
351
1	SIMPLE	t1	index	NULL	a	20	NULL	11	Using index
unknown's avatar
unknown committed
352
select * from t1 order by a, b, c;
unknown's avatar
unknown committed
353 354 355 356 357 358 359
a	b	c
1	0	
1	0	b
1	1	
1	1	b
1	1	b
1	3	b
360 361
2	1	a
2	1	b
unknown's avatar
unknown committed
362 363 364
2	2	a
2	2	b
2	3	c
unknown's avatar
unknown committed
365
explain select * from t1 order by a desc, b desc, c desc;
unknown's avatar
unknown committed
366
id	select_type	table	type	possible_keys	key	key_len	ref	rows	Extra
367
1	SIMPLE	t1	index	NULL	a	20	NULL	11	Using index
unknown's avatar
unknown committed
368
select * from t1 order by a desc, b desc, c desc;
369
a	b	c
unknown's avatar
unknown committed
370 371 372
2	3	c
2	2	b
2	2	a
373 374
2	1	b
2	1	a
unknown's avatar
unknown committed
375
1	3	b
376 377
1	1	b
1	1	b
unknown's avatar
unknown committed
378 379 380
1	1	
1	0	b
1	0	
unknown's avatar
unknown committed
381
explain select * from t1 where (a = 1 and b = 1 and c = 'b') or (a > 2) order by a desc;
unknown's avatar
unknown committed
382
id	select_type	table	type	possible_keys	key	key_len	ref	rows	Extra
383
1	SIMPLE	t1	range	a	a	20	NULL	3	Using where; Using index
unknown's avatar
unknown committed
384
select * from t1 where (a = 1 and b = 1 and c = 'b') or (a > 2) order by a desc;
385 386 387
a	b	c
1	1	b
1	1	b
unknown's avatar
unknown committed
388
explain select * from t1 where a < 2 and b <= 1 order by a desc, b desc;
unknown's avatar
unknown committed
389
id	select_type	table	type	possible_keys	key	key_len	ref	rows	Extra
unknown's avatar
unknown committed
390
1	SIMPLE	t1	range	a	a	4	NULL	6	Using where; Using index
unknown's avatar
unknown committed
391
select * from t1 where a < 2 and b <= 1 order by a desc, b desc;
392 393 394
a	b	c
1	1	b
1	1	b
unknown's avatar
unknown committed
395 396 397
1	1	
1	0	b
1	0	
unknown's avatar
unknown committed
398
select count(*) from t1 where a < 5 and b > 0;
unknown's avatar
unknown committed
399 400
count(*)
9
unknown's avatar
unknown committed
401
select * from t1 where a < 5 and b > 0 order by a desc,b desc;
unknown's avatar
unknown committed
402 403 404 405 406 407 408 409 410 411
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
412
explain select * from t1 where a between 1 and 3 and b <= 1 order by a desc, b desc;
unknown's avatar
unknown committed
413
id	select_type	table	type	possible_keys	key	key_len	ref	rows	Extra
unknown's avatar
unknown committed
414
1	SIMPLE	t1	range	a	a	8	NULL	10	Using where; Using index
unknown's avatar
unknown committed
415
select * from t1 where a between 1 and 3 and b <= 1 order by a desc, b desc;
416 417 418 419 420
a	b	c
2	1	b
2	1	a
1	1	b
1	1	b
unknown's avatar
unknown committed
421 422 423
1	1	
1	0	b
1	0	
unknown's avatar
unknown committed
424
explain select * from t1 where a between 0 and 1 order by a desc, b desc;
unknown's avatar
unknown committed
425
id	select_type	table	type	possible_keys	key	key_len	ref	rows	Extra
unknown's avatar
unknown committed
426
1	SIMPLE	t1	range	a	a	4	NULL	5	Using where; Using index
unknown's avatar
unknown committed
427
select * from t1 where a between 0 and 1 order by a desc, b desc;
428
a	b	c
unknown's avatar
unknown committed
429
1	3	b
430 431
1	1	b
1	1	b
unknown's avatar
unknown committed
432 433 434
1	1	
1	0	b
1	0	
unknown's avatar
unknown committed
435 436 437 438 439 440
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)
unknown's avatar
unknown committed
441
) ENGINE=MyISAM;
unknown's avatar
unknown committed
442 443 444 445 446 447 448 449 450
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)
unknown's avatar
unknown committed
451
) ENGINE=MyISAM;
unknown's avatar
unknown committed
452 453 454 455
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)
unknown's avatar
unknown committed
456
) ENGINE=MyISAM;
unknown's avatar
unknown committed
457 458 459
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;
460 461 462 463 464 465 466
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
467
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;
468 469 470 471 472 473 474
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
475
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;
unknown's avatar
unknown committed
476
id	select_type	table	type	possible_keys	key	key_len	ref	rows	Extra
unknown's avatar
unknown committed
477
1	SIMPLE	t1	index	PRIMARY	PRIMARY	4	NULL	6	Using index
478 479
1	SIMPLE	t2	eq_ref	PRIMARY,uid	PRIMARY	4	test.t1.gid	1	
1	SIMPLE	t3	eq_ref	PRIMARY	PRIMARY	2	test.t2.uid	1	Using where; Using index
unknown's avatar
unknown committed
480
EXPLAIN SELECT t1.gid, t3.uid from t1, t3 where t1.gid = t3.uid order by t1.gid,t3.skr;
unknown's avatar
unknown committed
481
id	select_type	table	type	possible_keys	key	key_len	ref	rows	Extra
unknown's avatar
unknown committed
482 483
1	SIMPLE	t3	ALL	PRIMARY	NULL	NULL	NULL	6	Using temporary; Using filesort
1	SIMPLE	t1	eq_ref	PRIMARY	PRIMARY	4	test.t3.uid	1	Using where; Using index
unknown's avatar
unknown committed
484
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;
unknown's avatar
unknown committed
485
id	select_type	table	type	possible_keys	key	key_len	ref	rows	Extra
unknown's avatar
unknown committed
486
1	SIMPLE	t1	index	PRIMARY	PRIMARY	4	NULL	6	Using index; Using temporary; Using filesort
487 488
1	SIMPLE	t2	eq_ref	PRIMARY,uid	PRIMARY	4	test.t1.gid	1	
1	SIMPLE	t3	eq_ref	PRIMARY	PRIMARY	2	test.t2.uid	1	Using where; Using index
unknown's avatar
unknown committed
489
EXPLAIN SELECT t1.gid, t3.uid from t1, t3 where t1.gid = t3.uid order by t3.skr,t1.gid;
unknown's avatar
unknown committed
490
id	select_type	table	type	possible_keys	key	key_len	ref	rows	Extra
unknown's avatar
unknown committed
491
1	SIMPLE	t3	ALL	PRIMARY	NULL	NULL	NULL	6	Using temporary; Using filesort
unknown's avatar
unknown committed
492
1	SIMPLE	t1	eq_ref	PRIMARY	PRIMARY	4	test.t3.uid	1	Using where; Using index
unknown's avatar
unknown committed
493
EXPLAIN SELECT t1.gid, t3.uid from t1, t3 where t1.skr = t3.uid order by t1.gid,t3.skr;
unknown's avatar
unknown committed
494
id	select_type	table	type	possible_keys	key	key_len	ref	rows	Extra
unknown's avatar
unknown committed
495
1	SIMPLE	t1	ALL	NULL	NULL	NULL	NULL	6	Using temporary; Using filesort
496
1	SIMPLE	t3	eq_ref	PRIMARY	PRIMARY	2	test.t1.skr	1	Using where
unknown's avatar
unknown committed
497
drop table t1,t2,t3;
498 499 500 501 502 503 504 505 506 507 508 509 510 511 512 513 514 515 516 517 518 519 520 521 522 523 524 525 526 527 528 529 530 531 532
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
533 534 535 536 537 538 539 540 541 542 543 544 545 546 547 548 549 550 551 552 553 554 555 556 557
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;
unknown's avatar
unknown committed
558 559 560 561 562 563 564 565 566 567 568 569 570 571 572 573 574 575 576
create table t1(id int not null auto_increment primary key, t char(12));
explain select id,t from t1 order by id;
id	select_type	table	type	possible_keys	key	key_len	ref	rows	Extra
1	SIMPLE	t1	ALL	NULL	NULL	NULL	NULL	1000	Using filesort
explain select id,t from t1 force index (primary) order by id;
id	select_type	table	type	possible_keys	key	key_len	ref	rows	Extra
1	SIMPLE	t1	index	NULL	PRIMARY	4	NULL	1000	
drop table t1;
CREATE TABLE t1 (
FieldKey varchar(36) NOT NULL default '',
LongVal bigint(20) default NULL,
StringVal mediumtext,
KEY FieldKey (FieldKey),
KEY LongField (FieldKey,LongVal),
KEY StringField (FieldKey,StringVal(32))
);
INSERT INTO t1 VALUES ('0',3,'0'),('0',2,'1'),('0',1,'2'),('1',2,'1'),('1',1,'3'), ('1',0,'2'),('2',3,'0'),('2',2,'1'),('2',1,'2'),('2',3,'0'),('2',2,'1'),('2',1,'2'),('3',2,'1'),('3',1,'2'),('3','3','3');
EXPLAIN SELECT * FROM t1 WHERE FieldKey = '1' ORDER BY LongVal;
id	select_type	table	type	possible_keys	key	key_len	ref	rows	Extra
577
1	SIMPLE	t1	ref	FieldKey,LongField,StringField	LongField	38	const	3	Using where
unknown's avatar
unknown committed
578 579 580 581 582 583 584
SELECT * FROM t1 WHERE FieldKey = '1' ORDER BY LongVal;
FieldKey	LongVal	StringVal
1	0	2
1	1	3
1	2	1
EXPLAIN SELECT * FROM t1 WHERE FieldKey > '2' ORDER BY LongVal;
id	select_type	table	type	possible_keys	key	key_len	ref	rows	Extra
585
1	SIMPLE	t1	range	FieldKey,LongField,StringField	FieldKey	38	NULL	4	Using where; Using filesort
unknown's avatar
unknown committed
586 587 588 589 590 591 592
SELECT * FROM t1 WHERE FieldKey > '2' ORDER BY LongVal;
FieldKey	LongVal	StringVal
3	1	2
3	2	1
3	3	3
EXPLAIN SELECT * FROM t1 WHERE FieldKey > '2' ORDER BY FieldKey, LongVal;
id	select_type	table	type	possible_keys	key	key_len	ref	rows	Extra
593
1	SIMPLE	t1	range	FieldKey,LongField,StringField	LongField	38	NULL	4	Using where
unknown's avatar
unknown committed
594 595 596 597 598 599 600 601 602 603 604 605 606 607 608 609 610 611 612 613 614 615 616 617 618 619 620 621 622 623 624 625 626 627 628 629 630 631 632 633 634 635 636 637 638 639 640 641 642 643 644 645 646 647 648 649 650 651 652 653 654 655 656 657 658 659 660 661 662
SELECT * FROM t1 WHERE FieldKey > '2' ORDER BY FieldKey, LongVal;
FieldKey	LongVal	StringVal
3	1	2
3	2	1
3	3	3
DROP TABLE t1;
CREATE TABLE t1 (a INT, b INT);
SET @id=0;
UPDATE t1 SET a=0 ORDER BY (a=@id), b;
DROP TABLE t1;
CREATE TABLE t1 (  id smallint(6) unsigned NOT NULL default '0',  menu tinyint(4) NOT NULL default '0',  KEY id (id),  KEY menu (menu)) ENGINE=MyISAM;
INSERT INTO t1 VALUES (11384, 2),(11392, 2);
SELECT id FROM t1 WHERE id <11984 AND menu =2 ORDER BY id DESC LIMIT 1 ;
id
11392
drop table t1;
create table t1(a int, b int, index(b));
insert into t1 values (2, 1), (1, 1), (4, NULL), (3, NULL), (6, 2), (5, 2);
explain select * from t1 where b=1 or b is null order by a;
id	select_type	table	type	possible_keys	key	key_len	ref	rows	Extra
1	SIMPLE	t1	ref_or_null	b	b	5	const	3	Using where; Using filesort
select * from t1 where b=1 or b is null order by a;
a	b
1	1
2	1
3	NULL
4	NULL
explain select * from t1 where b=2 or b is null order by a;
id	select_type	table	type	possible_keys	key	key_len	ref	rows	Extra
1	SIMPLE	t1	ref_or_null	b	b	5	const	4	Using where; Using filesort
select * from t1 where b=2 or b is null order by a;
a	b
3	NULL
4	NULL
5	2
6	2
drop table t1;
create table t1 (a int not null auto_increment, b int not null, c int not null, d int not null,
key(a,b,d), key(c,b,a));
create table t2 like t1;
insert into t1 values (NULL, 1, 2, 0), (NULL, 2, 1, 1), (NULL, 3, 4, 2), (NULL, 4, 3, 3);
insert into t2 select null, b, c, d from t1;
insert into t1 select null, b, c, d from t2;
insert into t2 select null, b, c, d from t1;
insert into t1 select null, b, c, d from t2;
insert into t2 select null, b, c, d from t1;
insert into t1 select null, b, c, d from t2;
insert into t2 select null, b, c, d from t1;
insert into t1 select null, b, c, d from t2;
insert into t2 select null, b, c, d from t1;
insert into t1 select null, b, c, d from t2;
optimize table t1;
Table	Op	Msg_type	Msg_text
test.t1	optimize	status	OK
set @row=10;
insert into t1 select 1, b, c + (@row:=@row - 1) * 10, d - @row from t2 limit 10;
select * from t1 where a=1 and b in (1) order by c, b, a;
a	b	c	d
1	1	2	0
1	1	12	-1
1	1	52	-5
1	1	92	-9
select * from t1 where a=1 and b in (1);
a	b	c	d
1	1	92	-9
1	1	52	-5
1	1	12	-1
1	1	2	0
drop table t1, t2;
663 664 665 666 667 668 669 670 671 672 673 674 675 676 677 678 679 680 681 682
create table t1 (col1 int, col int);
create table t2 (col2 int, col int);
insert into t1 values (1,1),(2,2),(3,3);
insert into t2 values (1,3),(2,2),(3,1);
select t1.* , t2.col as t2_col from t1 left join t2 on (t1.col1=t2.col2)
order by col;
col1	col	t2_col
1	1	3
2	2	2
3	3	1
select col1 as col, col from t1 order by col;
ERROR 23000: Column 'col' in order clause is ambiguous
select t1.col as c1, t2.col as c2 from t1, t2 where t1.col1=t2.col2
order by col;
ERROR 23000: Column 'col' in order clause is ambiguous
select t1.col as c1, t2.col as c2 from t1, t2 where t1.col1=t2.col2
order by col;
ERROR 23000: Column 'col' in order clause is ambiguous
select col1 from t1, t2 where t1.col1=t2.col2 order by col;
ERROR 23000: Column 'col' in order clause is ambiguous
683 684 685
select t1.col as t1_col, t2.col2 from t1, t2 where t1.col1=t2.col2
order by col;
ERROR 23000: Column 'col' in order clause is ambiguous
686 687 688 689 690 691 692 693 694 695 696 697 698 699 700 701 702 703 704 705 706 707 708 709 710 711 712 713 714 715 716 717 718 719 720 721 722
select t1.col as t1_col, t2.col from t1, t2 where t1.col1=t2.col2
order by col;
t1_col	col
3	1
2	2
1	3
select col2 as c, col as c from t2 order by col;
c	c
3	1
2	2
1	3
select col2 as col, col as col2 from t2 order by col;
col	col2
1	3
2	2
3	1
select t2.col2, t2.col, t2.col from t2 order by col;
col2	col	col
3	1	1
2	2	2
1	3	3
select t2.col2 as col from t2 order by t2.col;
col
3
2
1
select t2.col2 as col, t2.col from t2 order by t2.col;
col	col
3	1
2	2
1	3
select t2.col2, t2.col, t2.col from t2 order by t2.col;
col2	col	col
3	1	1
2	2	2
1	3	3
drop table t1, t2;
723 724 725 726 727 728 729 730 731 732 733 734 735
create table t1 (a char(25));
insert into t1 set a = repeat('x', 20);
insert into t1 set a = concat(repeat('x', 19), 'z');
insert into t1 set a = concat(repeat('x', 19), 'ab');
insert into t1 set a = concat(repeat('x', 19), 'aa');
set max_sort_length=20;
select a from t1 order by a;
a
xxxxxxxxxxxxxxxxxxxab
xxxxxxxxxxxxxxxxxxxaa
xxxxxxxxxxxxxxxxxxxx
xxxxxxxxxxxxxxxxxxxz
drop table t1;
unknown's avatar
unknown committed
736 737 738 739 740 741 742
create table t1 (a int not null, b  int not null, c int not null);
insert t1 values (1,1,1),(1,1,2),(1,2,1);
select a, b from t1 group by a, b order by sum(c);
a	b
1	2
1	1
drop table t1;
743 744 745 746 747 748 749 750 751 752 753 754 755 756 757 758 759 760 761 762 763 764 765 766 767 768 769 770 771 772 773 774 775 776 777 778 779 780
create table t1 (
`sid` decimal(8,0) default null,
`wnid` varchar(11) not null default '',
key `wnid14` (`wnid`(4)),
key `wnid` (`wnid`)
) engine=myisam default charset=latin1;
insert into t1 (`sid`, `wnid`) values
('10100','01019000000'),('37986','01019000000'),('37987','01019010000'),
('39560','01019090000'),('37989','01019000000'),('37990','01019011000'),
('37991','01019011000'),('37992','01019019000'),('37993','01019030000'),
('37994','01019090000'),('475','02070000000'),('25253','02071100000'),
('25255','02071100000'),('25256','02071110000'),('25258','02071130000'),
('25259','02071190000'),('25260','02071200000'),('25261','02071210000'),
('25262','02071290000'),('25263','02071300000'),('25264','02071310000'),
('25265','02071310000'),('25266','02071320000'),('25267','02071320000'),
('25269','02071330000'),('25270','02071340000'),('25271','02071350000'),
('25272','02071360000'),('25273','02071370000'),('25281','02071391000'),
('25282','02071391000'),('25283','02071399000'),('25284','02071400000'),
('25285','02071410000'),('25286','02071410000'),('25287','02071420000'),
('25288','02071420000'),('25291','02071430000'),('25290','02071440000'),
('25292','02071450000'),('25293','02071460000'),('25294','02071470000'),
('25295','02071491000'),('25296','02071491000'),('25297','02071499000');
explain select * from t1 where wnid like '0101%' order by wnid;
id	select_type	table	type	possible_keys	key	key_len	ref	rows	Extra
1	SIMPLE	t1	range	wnid14,wnid	wnid	13	NULL	10	Using where
select * from t1 where wnid like '0101%' order by wnid;
sid	wnid
10100	01019000000
37986	01019000000
37989	01019000000
37987	01019010000
37990	01019011000
37991	01019011000
37992	01019019000
37993	01019030000
39560	01019090000
37994	01019090000
drop table t1;