Sort by the values in an IN()?

[1Trackmine]

I am trying to sort by the values in a:

SELECT idCol FROM myTable WHERE idCol IN (5,7,2,3,16)

So the results would be:
5
7
2
3
16

Now, the results are:
2
3
5
7
16

The values in the IN() are identity column values generated by a Zend search resultant, and are generated in order of weight. This means I must retain that order when querying the database for the rest of the columns associated with the identity column.

Thanks much!!!!

[deepuv04]

I am trying to sort by the values in a:

SELECT idCol FROM myTable WHERE idCol IN (5,7,2,3,16)

So the results would be:
5
7
2
3
16

Now, the results are:
2
3
5
7
16

The values in the IN() are identity column values generated by a Zend search resultant, and are generated in order of weight. This means I must retain that order when querying the database for the rest of the columns associated with the identity column.

Thanks much!!!!

use

SELECT idCol FROM myTable WHERE idCol IN (5,7,2,3,16) order by idCol

[amitpatel66]

I am trying to sort by the values in a:

SELECT idCol FROM myTable WHERE idCol IN (5,7,2,3,16)

So the results would be:
5
7
2
3
16

Now, the results are:
2
3
5
7
16

The values in the IN() are identity column values generated by a Zend search resultant, and are generated in order of weight. This means I must retain that order when querying the database for the rest of the columns associated with the identity column.

Thanks much!!!!

No that is not possible by a simple select query.
You can either SORT ASCENDING or DESCENDING but not random
By the way the IN clause data is hardcoded?

[1Trackmine]

amitpatel66,

It's an ordered list of values returned by Zend Lucene. The order denotes the weight of the result.

So if you search for: "Sports Illustrated", your results might be:
"Sports Store"
"Illustrated Sports Images"
"Sports Illustrated"
"Sports Websites"
etc...

Each is assigned a weight (by Zend). And has a numeric value denoting the value of the corresponding ID column value in the database.

So:
ID | Results Text

837483 | "Sports Store"
82744 | "Illustrated Sports Images"
336764 | "Sports Illustrated"
326183 | "Sports Websites"

Zend returns it by weight. So since "Sports Illustrated" (2336764) is an exact match, it's returned first (but you will notice the ID is NOT first if we sort by ID column -- sorting by ID, in any way, does no good):

336764 | "Sports Illustrated"
837483 | "Sports Store"
326183 | "Sports Websites"
82744 | "Illustrated Sports Images"

I get from Zend: (336764, 837483, 326183, 82744). In that order. Meaning 336764 is closes match, 837483 is next closest, etc.

So I cannot use the ID column as the sort criteria. Only the actual position in the IN() part of the query.

---------

I am going to brute-force it. So if anyone need a less than optimal solution, here's what I am going to do:

1. Make a temp table:
CREATE TABLE TempZend(
IDCol INT,
Position TINYINT
);

2. Stick the Zend results into the table - in order:
INSERT INTO TempZend (336764, 1)
INSERT INTO TempZend (837483, 2)
INSERT INTO TempZend (326183, 3)
INSERT INTO TempZend (82744, 4)

3. Do a JOIN and sort by TempZend.Position.
SELECT * FROM myTable, TempZend WHERE myTable.idCol = TempZend.IDCol ORDER BY TempZend.Position

Sucks, yea. But will work.

If anyone comes up w/ a single kewl SELECT, please post here. I'll keep checking back.

Thanks!!

[amitpatel66]

amitpatel66,

It's an ordered list of values returned by Zend Lucene. The order denotes the weight of the result.

So if you search for: "Sports Illustrated", your results might be:
"Sports Store"
"Illustrated Sports Images"
"Sports Illustrated"
"Sports Websites"
etc...

Each is assigned a weight (by Zend). And has a numeric value denoting the value of the corresponding ID column value in the database.

So:
ID | Results Text

837483 | "Sports Store"
82744 | "Illustrated Sports Images"
336764 | "Sports Illustrated"
326183 | "Sports Websites"

Zend returns it by weight. So since "Sports Illustrated" (2336764) is an exact match, it's returned first (but you will notice the ID is NOT first if we sort by ID column -- sorting by ID, in any way, does no good):

336764 | "Sports Illustrated"
837483 | "Sports Store"
326183 | "Sports Websites"
82744 | "Illustrated Sports Images"

I get from Zend: (336764, 837483, 326183, 82744). In that order. Meaning 336764 is closes match, 837483 is next closest, etc.

So I cannot use the ID column as the sort criteria. Only the actual position in the IN() part of the query.

---------

I am going to brute-force it. So if anyone need a less than optimal solution, here's what I am going to do:

1. Make a temp table:
CREATE TABLE TempZend(
IDCol INT,
Position TINYINT
);

2. Stick the Zend results into the table - in order:
INSERT INTO TempZend (336764, 1)
INSERT INTO TempZend (837483, 2)
INSERT INTO TempZend (326183, 3)
INSERT INTO TempZend (82744, 4)

3. Do a JOIN and sort by TempZend.Position.
SELECT * FROM myTable, TempZend WHERE myTable.idCol = TempZend.IDCol ORDER BY TempZend.Position

Sucks, yea. But will work.

If anyone comes up w/ a single kewl SELECT, please post here. I'll keep checking back.

Thanks!!

This is for one scenario but what about other scenarios.
One more thing I would suggest is to use ORDER BY ROWID will work.
When you do an insert as mentioned in your previous post, in that order, a ROWID will be assigned to each row and ORDER by that ROWID will do. You need not have seperate column called POSITION.

Try this:

SELECT * FROM myTable, TempZend WHERE myTable.idCol = TempZend.IDCol ORDER BY ROWID