In this guide, we're going to talk about how we can find distinct values in a database. When I mean distinct I mean that we want to be able to filter out duplicates and SQL gives us a really nice interface for being able to make this possible. In the break, I added a few kind of close to duplicate records in the database. So right here you can see if you run select all from Guides.

Right now we have “my blog” and we have “my blog” that was added and you can see the full list. The very first one has an id of one and is called “my blog”. Now there are some other differences though. The user_id associated with the new one is different for one of them.

So we have a user with id of one for the first “my blog” and then a user with the id of two for the second. Then the revenue numbers are different for all three of them. So they're very close to being duplicates but not completely. That's important but subtle difference that is going to help us to determine what kind of query we're going to run.

Now the way to run a query that finds the unique values is you're going to say SELECT and then give the “distinct” keyword. When you say SELECT distinct this is going to tell SQL that I want you to remove duplicates from the query. Now you can pass what distinct means to you. You have to be explicit with this so you can say “guides_title from guides”.

Now when you run this query you’ll notice how at the moment we have five records. Now if I run this you'll see we only have three records. Now I didn't put the other items in there just because I wanted to isolate it down to what we're actually looking for. Right now we're removing any duplicate values in the guides_title. Any duplicate titles are not going to be covered in this query but we can make this even more specific.

Say that we wanted to grab the distinct items that have the same title but also were from the same user. In that case, I could say guides_users_id. Now if I run this code you'll see that now we're up to four records now. What we have is not just the regular distinct call that's looking at a title, but now we're also looking at the users_id.

This is a very important difference in that we are able to pick and choose which items we want to look for duplicates in. As you may have guessed we are now going to say guides and revenue. Now if I pull this in and run it you can see that now all five posts are returned because technically they weren't really duplicate posts.

They had subtle differences one had a different users_id and then all three of them had different revenues even though the titles all matched. That is how you can find distinct meaning of unique values in a SQL database query.

Code

USE devcamp_sql_course_schema;

SELECT *
FROM guides;

SELECT distinct guides_title, guides_users_id, guides_revenue
FROM guides;