Hi,
I am using SQLite 3.11. I create a table as follows:
CREATE TABLE MyTable (F1 INTEGER, F2
INTEGER, F3 INTEGER);
Then add the following records:
INSERT INTO MyTable (F1, F2, F3) Values (1,
2, 8);
INSERT INTO MyTable (F1, F2, F3) Values (1,
3, 9);
INSERT INTO MyTable (F1, F2, F3) Values (2,
4, 8);
INSERT INTO MyTable (F1, F2, F3) Values (2,
5, 2);
INSERT INTO MyTable (F1, F2, F3) Values (3,
6, 4);
INSERT INTO MyTable (F1, F2, F3) Values (3,
7, 8);
INSERT INTO MyTable (F1, F2, F3) Values (4,
2, 4);
Now if two records have the same value of F1, then I will define them as
conflict records.
I want to select all the records whose (F3 & 8) != 0 where (F3 & 8) means to
get the third bit of the F3 value. Moreover, for conflict records, I will
order them by F2 and only the record with the largest F2 will be selected.
So for the above sample, the following record will be selected:
(1, 3, 9)
(2, 4, 8)
(3, 7, 8)
How to do that?
I first try the following command:
SELECT * FROM MyTable WHERE ((F3 & 8) != 0)
ORDER BY F2 DESC;
But that will return:
(3, 7, 8)
(2, 4, 8)
(1, 3, 9)
(1, 2 8)
If I use the following command:
SELECT * FROM MyTable WHERE ((F3 & 8) != 0)
ORDER BY F2 DESC LIMIT 1;
Then it will return:
(3, 7, 8)
Both are incorrect.
Thanks
