Description
Submitted by: Oscar Giovani Blanco (bgracso)
Votes: 1
Firebird 2.5-super.
The clausule 'order by' is not working when you includes a column with a math calculation with the Rand() function, perhaps also Decode() function. The Query simpliest (because it belongs to another query bigger, that also is not ordering by 'Prbblty' field) is:
select QII.Id_Qstnry,
QII.Id_Qstn,
QII.Enabled,
QII.Seconds,
QII.Min_Score,
QII.Probability,
QII.Seq,
Decode(Sign(QII.Probability-Round(Rand()*100,0)),-1,0,1) Prbblty
from Questionary_Items QII
order by 8 desc
The DDL of Questionary_Items is:
CREATE TABLE QUESTIONARY_ITEMS (
ID_QSTNRY SMALLINT NOT NULL,
ID_QSTN SMALLINT NOT NULL,
ENABLED SMALLINT DEFAULT '1' NOT NULL,
SECONDS SMALLINT DEFAULT '60' NOT NULL,
MIN_SCORE SMALLINT DEFAULT '1' NOT NULL,
PROBABILITY SMALLINT DEFAULT '100' NOT NULL,
SEQ SMALLINT DEFAULT '0' NOT NULL);
ALTER TABLE QUESTIONARY_ITEMS ADD CONSTRAINT PK_QUESTIONARY_ITEMS PRIMARY KEY(ID_QSTNRY,ID_QSTN);
ALTER TABLE QUESTIONARY_ITEMS ADD CONSTRAINT FK_QUESTIONARY_ITEMS_00 FOREIGN KEY (ID_QSTNRY) REFERENCES QUESTIONARY(ID);
ALTER TABLE QUESTIONARY_ITEMS ADD CONSTRAINT FK_QUESTIONARY_ITEMS_01 FOREIGN KEY (ID_QSTN) REFERENCES QUESTIONS(ID);
ALTER TABLE QUESTIONARY_ITEMS ADD CONSTRAINT CK_QUESTIONARY_ITEMS_01 CHECK(Enabled in (0,1)
);
ALTER TABLE QUESTIONARY_ITEMS ADD CONSTRAINT CK_QUESTIONARY_ITEMS_02 CHECK(Seconds>0);
ALTER TABLE QUESTIONARY_ITEMS ADD CHECK (Seq>=0);
ALTER TABLE QUESTIONARY_ITEMS ADD CHECK (MIN_SCORE>=1);
Tha data to populate the table:
INSERT INTO QUESTIONARY_ITEMS VALUES (2, 1, 1, 10, 6, 98, 12);
INSERT INTO QUESTIONARY_ITEMS VALUES (2, 2, 1, 3, 10, 52, 5);
INSERT INTO QUESTIONARY_ITEMS VALUES (2, 3, 1, 3, 10, 25, 5);
INSERT INTO QUESTIONARY_ITEMS VALUES (2, 4, 1, 7, 8, 81, 8);
INSERT INTO QUESTIONARY_ITEMS VALUES (2, 5, 1, 2, 3, 98, 16);
INSERT INTO QUESTIONARY_ITEMS VALUES (2, 6, 1, 2, 9, 1, 17);
INSERT INTO QUESTIONARY_ITEMS VALUES (2, 7, 1, 8, 2, 72, 14);
INSERT INTO QUESTIONARY_ITEMS VALUES (2, 8, 1, 6, 7, 57, 18);
INSERT INTO QUESTIONARY_ITEMS VALUES (2, 9, 1, 9, 6, 63, 6);
INSERT INTO QUESTIONARY_ITEMS VALUES (2, 10, 1, 6, 5, 81, 13);
INSERT INTO QUESTIONARY_ITEMS VALUES (4, 11, 1, 11, 8, 89, 19);
INSERT INTO QUESTIONARY_ITEMS VALUES (4, 12, 1, 4, 2, 75, 11);
INSERT INTO QUESTIONARY_ITEMS VALUES (4, 13, 1, 1, 1, 99, 18);
INSERT INTO QUESTIONARY_ITEMS VALUES (4, 14, 1, 4, 4, 54, 4);
INSERT INTO QUESTIONARY_ITEMS VALUES (4, 15, 1, 4, 4, 68, 2);
INSERT INTO QUESTIONARY_ITEMS VALUES (4, 16, 1, 3, 10, 88, 10);
INSERT INTO QUESTIONARY_ITEMS VALUES (4, 17, 1, 6, 7, 2, 13);
INSERT INTO QUESTIONARY_ITEMS VALUES (4, 18, 1, 4, 2, 34, 17);
INSERT INTO QUESTIONARY_ITEMS VALUES (4, 19, 1, 7, 5, 57, 6);
INSERT INTO QUESTIONARY_ITEMS VALUES (4, 20, 1, 8, 5, 71, 7);
INSERT INTO QUESTIONARY_ITEMS VALUES (6, 21, 1, 9, 7, 58, 16);
INSERT INTO QUESTIONARY_ITEMS VALUES (6, 22, 1, 7, 3, 74, 0);
INSERT INTO QUESTIONARY_ITEMS VALUES (6, 23, 1, 9, 10, 90, 5);
INSERT INTO QUESTIONARY_ITEMS VALUES (6, 24, 1, 7, 2, 8, 5);
INSERT INTO QUESTIONARY_ITEMS VALUES (6, 25, 1, 5, 8, 98, 11);
INSERT INTO QUESTIONARY_ITEMS VALUES (6, 26, 1, 7, 8, 55, 8);
INSERT INTO QUESTIONARY_ITEMS VALUES (6, 27, 1, 7, 10, 83, 17);
INSERT INTO QUESTIONARY_ITEMS VALUES (6, 28, 1, 6, 9, 38, 17);
INSERT INTO QUESTIONARY_ITEMS VALUES (6, 29, 1, 2, 8, 72, 3);
INSERT INTO QUESTIONARY_ITEMS VALUES (6, 30, 1, 9, 6, 22, 17);
INSERT INTO QUESTIONARY_ITEMS VALUES (8, 31, 1, 10, 7, 42, 13);
INSERT INTO QUESTIONARY_ITEMS VALUES (8, 32, 1, 5, 8, 9, 10);
INSERT INTO QUESTIONARY_ITEMS VALUES (8, 33, 1, 8, 5, 98, 6);
INSERT INTO QUESTIONARY_ITEMS VALUES (8, 34, 1, 9, 4, 77, 8);
INSERT INTO QUESTIONARY_ITEMS VALUES (8, 35, 1, 10, 8, 95, 20);
INSERT INTO QUESTIONARY_ITEMS VALUES (8, 36, 1, 2, 5, 56, 3);
INSERT INTO QUESTIONARY_ITEMS VALUES (8, 37, 1, 1, 2, 72, 12);
INSERT INTO QUESTIONARY_ITEMS VALUES (8, 38, 1, 10, 7, 74, 3);
INSERT INTO QUESTIONARY_ITEMS VALUES (8, 39, 1, 3, 10, 19, 6);
INSERT INTO QUESTIONARY_ITEMS VALUES (8, 40, 1, 7, 10, 87, 18);
INSERT INTO QUESTIONARY_ITEMS VALUES (10, 41, 1, 7, 10, 72, 13);
INSERT INTO QUESTIONARY_ITEMS VALUES (10, 42, 1, 2, 8, 46, 17);
INSERT INTO QUESTIONARY_ITEMS VALUES (10, 43, 1, 6, 9, 74, 4);
INSERT INTO QUESTIONARY_ITEMS VALUES (10, 44, 1, 3, 9, 30, 5);
INSERT INTO QUESTIONARY_ITEMS VALUES (10, 45, 1, 11, 2, 34, 4);
INSERT INTO QUESTIONARY_ITEMS VALUES (10, 46, 1, 1, 6, 14, 18);
INSERT INTO QUESTIONARY_ITEMS VALUES (10, 47, 1, 4, 4, 86, 8);
INSERT INTO QUESTIONARY_ITEMS VALUES (10, 48, 1, 10, 7, 25, 10);
INSERT INTO QUESTIONARY_ITEMS VALUES (10, 49, 1, 7, 7, 74, 4);
INSERT INTO QUESTIONARY_ITEMS VALUES (10, 50, 1, 11, 4, 2, 1);