![]() ![]() Window functions are thus named because they perform a calculation across a set of table rows that are related to the current row. We can then select other columns by joining the email column to the unique ones returned by the sub-query: Using a Window FunctionĪnother, albeit slightly more advanced solution, is to use a Window function. Therefore, if we wanted to limit emails to one per customer, we could include a sub-query that groups emails by customer_id. When combined with a function such as MIN or MAX, GROUP BY can limit a field to the first or last instance, relative to another field. The GROUP BY clause applies aggregate functions to a specific subset of data by grouping results according to one or more fields. So, what does work? Let's find out! Using Group By If we were now to add the DISTINCT clause to a query whose field list contains other columns, it does not work because the row as a whole is unique: ![]() Here's a screenshot in Navicat Premium's Grid view that shows a customer that has 2 associated emails: For that purpose, I added some extra emails to the Sakila Sample Database's customer table. In order to test out our queries, we'll need a table that contains duplicate data. ![]() We'll be taking a look at a couple of those here today. That being said, there are ways to remove duplicate values from one column, while ignoring other columns. Since DISTINCT operates on all of the fields in SELECT's column list, it can't be applied to an individual field that are part of a larger group. Applying Select Distinct to One Column Only by Robert GravelleĪdding the DISTINCT keyword to a SELECT query causes it to return only unique values for the specified column list so that duplicate rows are removed from the result set. ![]()
0 Comments
Leave a Reply. |
AuthorWrite something about yourself. No need to be fancy, just an overview. ArchivesCategories |