possible to use DISTINCT with wildcard?

(1) By chovy (chovyfu) on 2022-07-15 16:06:02 [link] [source]

I'm noticing if I do this I still get duplicate urls:

  SELECT DISTINCT h.url, h.*. p.word FROM search_history as h
    INNER JOIN users u, positive p ON h.user_id = u.id AND h.search_id = p.search_id WHERE p.word = ? AND h.created_at > ? ORDER BY h.created_at DESC

I need all the h.* fields but I want to only get unique h.url in results.

If I do this it works, but then I'd have to manually provide all the h.* fields

  SELECT DISTINCT h.url, h.title p.word FROM search_history as h
    INNER JOIN users u, positive p ON h.user_id = u.id AND h.search_id = p.search_id WHERE p.word = ? AND h.created_at > ? ORDER BY h.created_at DESC

(2) By David Raymond (dvdraymond) on 2022-07-15 17:51:20 in reply to 1 [source]

For your case, consider using a "GROUP BY h.url" to get only 1 row per url.

https://www.sqlite.org/quirks.html#aggregate_queries_can_contain_non_aggregate_result_columns_that_are_not_in_the_group_by_clause

"If a query contains no aggregate functions at all, then a GROUP BY clause can be added as a substitute for the DISTINCT ON clause. In other words, output rows are filtered so that only one row is shown for each distinct set of values in the GROUP BY clause. If two or more output rows would have otherwise had the same set of values for the GROUP BY columns, then one of the rows is chosen arbitrarily. (SQLite supports DISTINCT but not DISTINCT ON, whose functionality is provided instead by GROUP BY.)"