Usage of Distinct key word, SQL

Question

When i write query like:

SELECT DISTINCT City FROM Customers;

It is returning all different cities in Customers table.

But when i write query like:

SELECT DISTINCT City,* FROM Customers;

It is returning all the rows in Customers table(with city column first and all columns including city later in the output).

What is the reason for displaying all the records even if we use distinct keyword?

because in the second one you are selecting all/everything from that table with the all character * ? — CRABOLO, Dec 28 '14 at 05:18
@Sompuperoo so you mean to say that priority of * is more than that of distinct??? — svkvvenky, Dec 28 '14 at 05:19
@svkvvenky - What behavior do you expect? Each city to be listed once, with a random associated row? Or do you want the first record for each city, using, for example Select Top 1 * over (City), like this: http://stackoverflow.com/questions/6841605/get-top-1-row-of-each-group — David Manheim, Dec 28 '14 at 05:22

David Manheim · Accepted Answer · 2014-12-28T05:35:02.883

5

The DISTINCT keyword doesn't apply to a single column, it applies to the entire record. If the rows of your table are not identically valued for all columns, they are not distinct.

Let's assume there is some unique column in your customers table, ID. If you just want the latest customer ID per city, you could try:

SELECT Customers.* FROM
Customers JOIN (
  SELECT city, MAX(ID) as id
  FROM Customers
  GROUP BY city
  ) as max_id 
on Customers.ID=max_id.id

edited Dec 28 '14 at 05:35

answered Dec 28 '14 at 05:20

David Manheim

2,553
2
27
42

I am applying distinct keyword only on city column.
Can you elaborate your answer? – svkvvenky Dec 28 '14 at 05:23
@svkvvenky There isn't a way to apply the keyword to only a single column. That's not what SQ does. Instead, you need to clarify what you expect; if you have more than one customer from each city, which record for that city would be returned? SQL can't know what you wanted - it thinks you want distinct records, not distinct cities. – David Manheim Dec 28 '14 at 05:25
Then what do you say for this: SELECT DISTINCT *,City FROM Customers; – svkvvenky Dec 28 '14 at 05:28
That is the same query, as far as SQL knows. (The column orders returned do change, but that's the only difference.) – David Manheim Dec 28 '14 at 05:30
@svkvvenky - As I mentioned elsewhere, what do you want to see? A particular record for each distinct city, or just any single record for that city? – David Manheim Dec 28 '14 at 05:31
See my edited reply with a way to do something like what I think you want. – David Manheim Dec 28 '14 at 05:35
1

I am seeing possible combinations of distinct keyword.While doing so i got this doubt.Anyways thank you very much for explaining the execution of the query. – svkvvenky Dec 28 '14 at 05:37

tvanfosson · Answer 2 · 2014-12-28T05:31:33.127

It would only display all the rows if each row was unique in the database. The DISTINCT keyword says "eliminate exact duplicates" in the output set. In your second query, you're selecting City, then via the *, all of the columns in the table. Say you had columns City, State, Zip, then City, * is the same as City, City, State, Zip. Now, it would only return the unique combinations of these for all rows in the database. So if you had

Name, City, State, Zip
Joe, Chicago, IL, 60290
Steve, Chicago, IL, 60290
Joe, Chicago, IL, 60290
Joe, Los Angeles, CA, 90012

And selected City, * you would get

Joe, Chicago, IL, 60290
Steve, Chicago, IL, 60290
Joe, Los Angeles, CA, 90012

with the duplicate row Joe, Chicago, IL, 60290 eliminated.

If you are getting all rows, I suspect it's because you have a unique column (perhaps a primary key) in the table schema that forces each row to be unique.

What you might want instead is a GROUP BY query, but then you'll need to apply an aggregation operator to the remaining columns to choose which values for them you want.

SELECT State, COUNT(*) as [Customers in State]
FROM Customers
GROUP BY State

That will return a set of distinct states with the count of the number of rows corresponding to that state in the table.

He wants to return the distinct city only, as he explained in his comment to the earlier response. That's not the specified behavior for DISTINCT, and without clarification about what he wanted returned when selecting additional rows in addition to the distinct one, I'm not sure how to help. — David Manheim, Dec 28 '14 at 05:29

score 1 · Answer 3 · answered Dec 28 '14 at 05:19

IN

 SELECT DISTINCT City FROM Customers;// you just selecting distinct city from table

IN

 SELECT DISTINCT City,* FROM Customers; //you select distinct city and again * means all 
                                          from table (including city also)

N.B: Here the priority of * is more than DISTINCT and DISTINCT is executed on your tuple(single rows not just your city column)

Usage of Distinct key word, SQL

3 Answers3