Use DISTINCT with two columns (or something that works)

Question

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?

which engine + give a proper expected resulset. Rigorous questions give rigorous results, and the contrary too. — Sebas, Jul 23 '13 at 14:01

score 2 · Answer 1 · answered Jul 23 '13 at 14:01

2

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.

answered Jul 23 '13 at 14:01

Robert

25,425
8
67
81

Do not use group by instead of distinct: http://stackoverflow.com/a/11416345/1291428 – Sebas Jul 23 '13 at 14:03
@Sebas Yes I know, I added information when group by is usfel – Robert Jul 23 '13 at 14:05
I was using SELECT DISTINCT(sate, stateid) .... that's why it wouldnt work, because of the () – JuanBonnett Jul 23 '13 at 14:10
@JuanBonnett Yes It doesn't work with `()`, Don't forget to accept a chosen answer to avoid low accepted rate an close the question. – Robert Jul 23 '13 at 14:25

score 2 · Accepted Answer · answered Jul 23 '13 at 14:02

2

Just do something like this:

SELECT DISTINCT state, stateid
FROM table

answered Jul 23 '13 at 14:02

DarkAjax

15,955
11
53
65

1

I feel bad now that I see it was that easy... I mean, I was trying to use DISTINCT (sate, stateid) with PARENTESIS... May be that's why it wouldn't work. Thank you! – JuanBonnett Jul 23 '13 at 14:08
@JuanBonnett don't worry just try reading the documentation for whatever DBMS you're using next time you have a doubt or are unsure of the syntax, believe me it helps a lot! :) – DarkAjax Jul 23 '13 at 14:10

score 1 · Answer 3 · answered Jul 23 '13 at 14:01

1

Have you tried this?

select distinct stateid, state
from CityStates cs

answered Jul 23 '13 at 14:01

Gordon Linoff

1,242,037
58
646
786

score 1 · Answer 4 · answered Jul 23 '13 at 14:02

1

Select Distinct stateid, state  
from myTable

answered Jul 23 '13 at 14:02

Hedinn

864
4
7

score 1 · Answer 5 · answered Jul 23 '13 at 14:06

1

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

answered Jul 23 '13 at 14:06

VitaliyG

1,837
1
11
11

Use DISTINCT with two columns (or something that works)

5 Answers5