Use the min function (since they are all have the same value) -Jeff
-----Original Message----- From: sqlite-users-boun...@sqlite.org [mailto:sqlite-users-boun...@sqlite.org] On Behalf Of johnny depp (really!) Sent: Tuesday, February 17, 2009 3:44 AM To: sqlite-users@sqlite.org Subject: [sqlite] Little SQL help please.. Hello, I'm investigating if i should uses sqlite from within java? I need an embedded database, no fancy user management or anything... I'm only interested in speed. Sqlite looks good for the job..So I went out and found the someone has written a java sqlite wrapper. Great! But I ran into a problem: I need to group some rows, and if the values for a column are all the same I want to return that value, else I want to return "not the same".. like so: For this table: col1, col2 A "1" A "2" B "1" B "1" And the query: select ..... group by col1; // can't write the query because I don't know it.. I would like the result to be: col1 col2 A "not the same" B "1" I think the query should be this: SELECT col1, CASE count(distinct col2) > 1 THEN "not the same" ELSE first(col2) END AS col2 FROM table GROUP BY col1 The problem is the aggregation function first does not exist, this would not be a problem in c or c++ because I would plug it in the system (I read), the problem is I'm calling sqlite from java and so I can't plug in my own functions.. Or I will have to look into jni and i don't want to do that..seem scary stuff.. So my questions is, is there another way to do it without using the (none existing) function first? Kind Regards, Nick. -- View this message in context: http://www.nabble.com/Little-SQL-help-please..-tp22052925p22052925.html Sent from the SQLite mailing list archive at Nabble.com. _______________________________________________ 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
