Hello, I would like get the last 10 distinct (i'm not interested in all columns) rows in a table which meet a condition. My first try was to use the following query:
SELECT DISTINCT frobnitz.Field1, frobnitz.Field2, frobnitz.Field3, frobnitz.Field4 FROM frobnitz, user WHERE user.username = 'foo' AND frobnitz.customernumber=user.customernumber ORDER BY Field5 DESC LIMIT 0,100 (Field5 is the index on frobnitz). For curiosity i tested the result when doing the same query without the distinct. The strange thing is, that if i do this i get some rows as result which don't show up when using DISTINCT. How can this be? IMO _all_ rows which are in the result when not using distinct should be in the result too when using distinct. As a workaround I am now using the following query which seems to work: SELECT DISTINCT MAX(Field5) AS Field5_MAX, frobnitz.Field1, frobnitz.Field2, frobnitz.Field3, frobnitz.Field4 FROM frobnitz, user WHERE user.username = 'foo' AND frobnitz.customernumber=user.customernumber GROUP BY frobnitz.Field1, frobnitz.Field2, frobnitz.Field3, frobnitz.Field4 ORDER BY Field5_MAX DESC LIMIT 0,100 Why does the first query fail? Is this a bug in MySQL? I am using version 3.23.49 (which is included in Debian Woody). Thanks for your help. Hedonist; -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe: http://lists.mysql.com/[EMAIL PROTECTED]
