Add a group by name, hash and change the select to be name, min(setid), hash?
-----Original Message----- From: sqlite-users-boun...@sqlite.org [mailto:sqlite-users-boun...@sqlite.org] On Behalf Of Paul Sanderson Sent: Thursday, January 31, 2013 4:48 PM To: General Discussion of SQLite Database Subject: Re: [sqlite] SQL query Still playing with this I have the following table and I run the following query - the results of which are what I expect name, num, md5 sqlite> select * from rtable; $RmMetadata|0|8465-CEEF-126A-0F04-1EDC-1D7B-331F-9279 $RmMetadata|1|8465-CEEF-126A-0F04-1EDC-1D7B-331F-9279 $RmMetadata|2|8465-CEEF-126A-0F04-1EDC-1D7B-331F-9279 $RmMetadata|3|B2F9-B5D4-A427-9FE2-9724-BF95-1571-7CE5 $RmMetadata|4|0546-4667-5A69-6478-FC97-6F27-840D-7D62 $RmMetadata|5|0546-4667-5A69-6478-FC97-6F27-840D-7D62 $RmMetadata|6|0546-4667-5A69-6478-FC97-6F27-840D-7D62 $RmMetadata|7|01EE-7E2E-2242-E734-B125-D02F-A7F0-DC29 $RmMetadata|8|01EE-7E2E-2242-E734-B125-D02F-A7F0-DC29 sqlite> select * from rtable where num > 0 and md5 not in (select md5 sqlite> from rtable where num = 0); $RmMetadata|3|B2F9-B5D4-A427-9FE2-9724-BF95-1571-7CE5 $RmMetadata|4|0546-4667-5A69-6478-FC97-6F27-840D-7D62 $RmMetadata|5|0546-4667-5A69-6478-FC97-6F27-840D-7D62 $RmMetadata|6|0546-4667-5A69-6478-FC97-6F27-840D-7D62 $RmMetadata|7|01EE-7E2E-2242-E734-B125-D02F-A7F0-DC29 $RmMetadata|8|01EE-7E2E-2242-E734-B125-D02F-A7F0-DC29 What I want to do is return only one unique row for each MD5 - so the return set would be, this bit is failing me $RmMetadata|3|B2F9-B5D4-A427-9FE2-9724-BF95-1571-7CE5 $RmMetadata|4|0546-4667-5A69-6478-FC97-6F27-840D-7D62 $RmMetadata|7|01EE-7E2E-2242-E734-B125-D02F-A7F0-DC29 Thanks _______________________________________________ sqlite-users mailing list sqlite-users@sqlite.org http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users _______________________________________________ sqlite-users mailing list sqlite-users@sqlite.org http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
