In many cases we store some additional data in tables, which are helpful for some management tasks. But sometimes those data are problematic, especially if we would lie to have unique data.
Lets think about following case. We have table (user_log_table) in our database where we store all login and logout actions of our users. For each user action we store: username, timestamp, action code.
The case is to get all users which are active in our system (those who login at least once). Disctinct will not help use, because timestamp will differ for each row. But the solution is quite simple and goes to one query:
SELECT * FROM user_log_table WHERE ROWID IN (SELECT MAX(ROWID) FROM user_log_table GROUP BY username);
The query above will clearly return us all distincted results for username column.