I have the query below with one column in the final output coming from the main query and two columns in the final output coming from correlated subqueries:
SELECT DISTINCT *modenumber*, (SELECT tripid FROM trip WHERE modenumber = T.modenumber ORDER BY distance DESC LIMIT 3 ) AS *tripid*, (SELECT distance FROM trip WHERE modenumber = T.modenumber ORDER BY distance DESC LIMIT 3 ) AS *distance* FROM trip T ORDER BY modenumber; I expected this query to produce at least 3x the number of distinct modenumbers because the results of the subqueries (especially the tripid) are different for each trip. However, when I run it, I get only one row per modenumber. It is as if the DISTINCT keyword is applying only to the modenumber, and not to the other two columns in the select. Is that because the other two columns are coming from subqueries? The documentation does not explicitly mention subqueries, and only says distinct removes duplicate rows. In this case, it seems to think that the row consists of just the column from the main query and does not include the columns from the subqueries. Is this a bug or is this the expected behavior of the DISTINCT keyword? Thank you. Balaji Ramanathan _______________________________________________ sqlite-users mailing list sqlite-users@mailinglists.sqlite.org http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users
