select f1,
(select max(f2)
from mytable
where f1 = o.f1
and (f3 & 8)) as f2
from mytable as o
where (f3 & 8)
group by f1;
> -----Original Message-----
> From: sqlite-users-bounces at mailinglists.sqlite.org [mailto:sqlite-users-
> bounces at mailinglists.sqlite.org] On Behalf Of admin at shuling.net
> Sent: Sunday, 21 February, 2016 19:43
> To: sqlite-users at mailinglists.sqlite.org
> Subject: [sqlite] Select top 1 from duplicate values
> Importance: High
> Sensitivity: Confidential
>
> 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
>
>
>
> _______________________________________________
> sqlite-users mailing list
> sqlite-users at mailinglists.sqlite.org
> http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users
