CODEWITHSUNDEEP
sqldistinct
JuanBonnett
JuanBonnett

Reputation: 786

Use DISTINCT with two columns (or something that works)

I got a table with Cities and States it looks something like:

cityid    |city    |stateid    |state
--------------------------------------
0566      |abriaqui|05         |antioquia
0599      |alejand |05         |antioquia
0511      |another |05         |antioquia
0782      |dummy1  |09         |caldas
0783      |dummy2  |09         |caldas
0785      |dummy3  |09         |caldas
0788      |dummy4  |03         |Narino
0767      |dummy5  |03         |Narino
0734      |dummy6  |03         |Narino

As you can see I have Cities with uniques IDs, and the state they belong to is just to the right, with the state id and the state name. I would like to make a query that returns something similar to DINSTINCT(state):

STATE
--------------------
antioquia
caldas
narino

But I need the stateid too. How can I reach this?

Upvotes: 2

Views: 131

Answers (5)

VitaliyG
VitaliyG

Reputation: 1857

You should rearrange your database to store states in separate table STATES(id, name) and CITIES(id, name, state_id), then for selecting states you can use

SELECT id, name from STATES

Upvotes: 1

Hedinn
Hedinn

Reputation: 864

Select Distinct stateid, state  
from myTable

Upvotes: 1

DarkAjax
DarkAjax

Reputation: 16223

Just do something like this:

SELECT DISTINCT state, stateid
FROM table

Upvotes: 2

Robert
Robert

Reputation: 25753

Try this way:

select distinct state, stateid
from tab

you can also use group by clause

select state, stateid
from tab
group by state, stateid

Group by is useful if you need some aggregation function. Solution is depend of your requirements.

Upvotes: 2

Gordon Linoff
Gordon Linoff

Reputation: 1269543

Have you tried this?

select distinct stateid, state
from CityStates cs

Upvotes: 1

Related Questions