CODEWITHSUNDEEP
sqlpostgresql
Tanishq Daiya
Tanishq Daiya

Reputation: 1

SQL Query to find different city with any city code

I have a table with states, and district code.

id name code
1 Nokha 101
2 Nokha 105
3 Bajju 107
4 Bajju 109
5 Byawar 175
6 Bajju 171

Now, I an output something like this

id name code
1 Nokha 101
3 Bajju 107
4 Bajju 109
5 Byawar 175

I mean, I want to have unique city with a code which doesnot get repeated.

So, for your help, I have some code:

create table tbl_thana
(
    id   serial not null,
    name varchar,
    code integer
);

alter table tbl_thana
    owner to postgres;

INSERT INTO public.tbl_thana (id, name, code) VALUES (1, 'Nokha', 101);
INSERT INTO public.tbl_thana (id, name, code) VALUES (2, 'Nokha', 105);
INSERT INTO public.tbl_thana (id, name, code) VALUES (3, 'Bajju', 107);
INSERT INTO public.tbl_thana (id, name, code) VALUES (4, 'Bajju', 109);
INSERT INTO public.tbl_thana (id, name, code) VALUES (5, 'Byawar', 175);
INSERT INTO public.tbl_thana (id, name, code) VALUES (6, 'Bajju', 171);

Upvotes: 0

Views: 438

Answers (1)

Tim Biegeleisen
Tim Biegeleisen

Reputation: 521569

It seems that you want one record for each state, having some code, possibly the earliest one. If so, then you may use:

SELECT DISTINCT ON (name) id, name, code
FROM public.tbl_thana
ORDER BY name, code;

Note that your expected output has state Bajju appearing twice. This is either a typo, or you have additional rules not stated.

Upvotes: 1

Related Questions