SQL Step-by-Step (distinct) and exclude replication

Distinct and excluding replication

If you like to list all the IDs and names you've bought, it's obvious that you may be listing all your customers without considering that some customers are buying too many antiques, so you'll find that some of the data is duplicated. This means that you need to notify SQL to exclude replicated rows, regardless of how many antiques the customer has bought, but only once. To achieve this, you can use the DISTINCT keyword.

First we need to have a equijoin for the Antiqueowners table to get the customer's LastName and initial detail data. However, you should consider that the Sellerid column in the Antiques table is an outer code of the Antiqueowners table, so the customer can only list the rows of IDs and names in the Antiqueowners table. We also want to exclude Sellerid replication from the column's data, so we want to use distinct on duplicate columns.

To prevent replication, we also want to arrange the LastName in alphabetical order, and then arrange the FirstName in alphabetical order ownerID, so we must also use the Orders by clause as follows:

SELECT DISTINCT Sellerid, Ownerlastname, Ownerfirstname

From Antiques, antiqueowners

WHERE Sellerid = ownerID

Order by Ownerlastname, Ownerfirstname, ownerID

In this case, because everyone buys an antique, we list all the antiques owners in alphabetical order Lasname.