Locked lesson.

Upgrade

About this lesson

We can use DISTINCT to only return distinct or different values in a SELECT statement

Exercise files

Download this lesson’s related exercise files.

SELECT DISTINCT.docx
58.9 KB SELECT DISTINCT - Solution.docx
59.4 KB

Quick reference

SELECT DISTINCT

Select Distinct allows us to return unique results.

When to use

Use it any time you want to find unique items.

Instructions

Select Distinct looks like this:

SELECT DISTINCT [First Name]
  FROM [Customer].[dbo].[Current_Customers]

This will return only the unique first names in the customer database. For example if there are 5 people named John and 3 people named Sally, each of these names will only be displayed once in the results (not 5 and 3 times).

You can also use COUNT to count the number of unique records.

SELECT COUNT ( DISTINCT [First Name] )
  FROM [Customer].[dbo].[Current_Customers]

This will return the total number of unique first names in the customer database.

Hints & tips

  • Select Distinct lets you return unique results
  • Be sure to specify a column, it can't return wildcard *
Login to download
  • 00:05 >> So we've been dealing with the select statement.
  • 00:07 We know all about that.
  • 00:08 It's our main statement, it's our main clause, our main SQL thing so far.
  • 00:13 And if we get rid of some of this, just delete this real quick here.
  • 00:19 We can see our basic select statement if we execute this, boom,
  • 00:22 it just shows us everything.
  • 00:23 Well, there's also something called select distinct and
  • 00:26 as you might guess, distinct means unique.
  • 00:29 So, with select distinct what will happen is we'll only returns things that
  • 00:33 are unique.
  • 00:34 So very useful for a lot of different reasons.
  • 00:37 And let's just go ahead and check this out in action.
  • 00:39 So we just go select distinct and
  • 00:43 you need to give it a range, you need to tell it what to search for.
  • 00:47 In fact if you just search for star you'll get a big red error.
  • 00:50 So you have to give it parameters, so let's go
  • 00:54 country If we execute this we get Canada, Mexico, and USA.
  • 00:59 So what it's doing it's just return the unique records of the countries.
  • 01:04 So we have a bunch of USAs, a bunch of Canadas, and we have a Mexico.
  • 01:08 So we get boom, boom, boom.
  • 01:09 Those three things.
  • 01:10 In fact If we remove that distinct,
  • 01:13 you can see we'll get USA, USA, USA, Canada, Canada, and Mexico.
  • 01:17 So that's really cool.
  • 01:18 And if you think about it, there are just a ton of times when you'll only want
  • 01:21 unique results, and select distinct in the perfect tool to do that.
  • 01:25 So what else can you do with this thing?
  • 01:27 Well, if we play around with this, it's all well and
  • 01:30 good to see a report that shows the distinct countries but
  • 01:33 what if we wanna know exactly how many distinct countries exist?
  • 01:37 In all report we just go distinct, we can see, we can just eyeball this, and
  • 01:42 see there's three but in the real world we may have 140 countries,
  • 01:47 you know 80 countries, whatever.
  • 01:50 You don't wanna go through and actually eyeball it and count them by hand.
  • 01:53 Or you could be searching for something completely different,
  • 01:55 you could have thousands of unique results.
  • 01:57 Even you know tens of thousands, hundreds of thousands.
  • 01:59 So we need a way to sort of count and see exactly how many unique things are so
  • 02:04 we can do that.
  • 02:05 I'll just go count, and then we just wrap everything in these parenthesis and
  • 02:09 we execute boom, just like you'd expect you get three, so that select distinct,
  • 02:14 and now there are some limitations that you should know about with this,
  • 02:17 we've used text as a data type in the past.
  • 02:20 And if we pull up our design, we have these text things.
  • 02:23 And we've run into some problems here and there using text.
  • 02:26 We'll do the same thing this time using select distinct,
  • 02:29 we'll see a problem with text.
  • 02:30 So let's just pick one last name.
  • 02:32 And if we come back here, let's get rid of this.
  • 02:37 And let's just type in last name.
  • 02:40 And sure enough,
  • 02:41 we get a big red error because text is not compatible with select distinct.
  • 02:45 So another reason to keep in mind,
  • 02:47 maybe you might not want to use text in the future, varchar works perfectly for.
  • 02:51 If we look back here, we have int as an age.
  • 02:55 We can do Age.
  • 02:57 And that works perfectly fine.
  • 03:00 So text, not so good, integers and varchar works perfectly fine with distinct.
  • 03:05 So that select distinct's a very useful way to find unique records in any column.
  • 03:09 In the next video, we'll look at the union operator.

Lesson notes are only available for subscribers.

Gift this course