CODEWITHSUNDEEP
sqlsql-server
AuthenticReplica
AuthenticReplica

Reputation: 870

SQL filtering records by unique column using WHERE clause only

I'm trying to filter a table to show only the distinct values of a column using only the WHERE clause of an SQL statement.

Below is an example table:

Person ID  |  Name  |  City  
----------------------------
    1       person1   city1
    2       person2   city1      
    3       person3   city2
    4       person4   city3
    5       person5   city1

I want to get the distinct cities like the following output:

City
----
city1
city2
city3

However my application restricts me to only specify a WHERE clause. So the basic format that I'm allowed is this:

select city from table1 where __________;

UPDATE: I've also tried and found that the SELECT statement is taking all columns (*) as opposed to that single column so I cannot use:

select * from table1 where 1=1 group by city;

Upvotes: 0

Views: 4659

Answers (3)

pari2k16
pari2k16

Reputation: 1

SQL provides a keyword 'DISTINCT' to show only the distinct values of a column. That is a direct way and there is no need of using WHERE clause. Another case would be you can use GROUP BY clause along with WHERE clause to get the distinct values something like: select city from table1 where 1=1 group by city;

Upvotes: -1

Vladimir Baranov
Vladimir Baranov

Reputation: 32695

Something like this should do it:

select * 
from table1 
where 
    PersonID IN
    (
        SELECT MIN(PersonID) AS MinID
        FROM table1
        GROUP BY City
    )

Upvotes: 1

Martin Smith
Martin Smith

Reputation: 453298

This in a where clause will have the desired effect. 

PersonId IN (SELECT MAX(PersonId) FROM table1 GROUP BY city)

Upvotes: 2

Related Questions