CODEWITHSUNDEEP
sqlpostgresql
Jason
Jason

Reputation: 794

SQL query to return only first occurance of one column value

I am not sure how to ask this question... but here it goes.

I have a table called lists that has four columns, id, name, type, and group. The type column values are not unique and is a text column. I need a query to return id and type from table lists, but I only need one of each type. It should also be in alphabetical order. It can be the first occurrence of each type.

In postgreSQL 9.4.5 I have tried select distinct list_type, id from lists order by list_type;, but this of course returns duplicate types that have unique ids.

I only need the first occurrence of each type and that id.

Some sample data:


                  id                  |           type            
--------------------------------------+--------------------------------
 0014dea9-f73f-46d0-bf7d-d52717029582 | invest_fees
 004665b5-4657-4cbc-8534-1aa9e4ef305f | invest_fees
 00910f6c-bdf0-4991-ac0c-969b3b9c6b84 | invest_fees
 009ed283-531b-4d7b-b51d-0c0e0e7a5707 | countries
 00b4f8e2-ae47-4083-ae6e-8d6dbaa2cd63 | invest_fees
 00ca1972-cf70-4fa2-bfde-bc89fce19433 | invest_fees
 00feb6a2-4ee7-4e31-8780-cb5938587669 | countries
 010777bc-7e74-4c13-8808-4c35cfdbf988 | pick_banks
 01852693-7560-4de5-a534-0519e7c04c51 | countries
 01bee5a4-23f7-427d-9b84-4c707154a812 | countries
 01bf29f9-70af-4b3c-b7f9-d01e0f0f142c | invest_fees
 01d51fe3-4c32-4d21-b38c-8e84a92ff0aa | invest_fees
 01d981dd-13d4-4098-a7e3-bd1bb5f02f2b | countries
 01de77bb-ff82-4c3c-b26f-f3829d84dd29 | invest_fees
 01df6e6c-9a77-4b83-a825-09949768df54 | countries
 01f11d01-f490-48a9-b21c-803f7c03f5f6 | invest_mtos

Upvotes: 1

Views: 14614

Answers (4)

Jan
Jan

Reputation: 89

In postgres you can use 

SELECT DISTINCT ON (type) type, many, other, columns FROM lists ORDER BY type ASC;

This should select only as many rows as there are different types. In your case there would be only one row where the type is invest_fees

You can read more about selections with DISTINC ON right here

Upvotes: 3

Vamsi Prabhala
Vamsi Prabhala

Reputation: 49260

You can use the row_number function to get one id (ordered ascending) per type. 

select id, type from 
(
select id, name, type, "group", 
row_number() over(partition by type order by type) as rn
from lists
) t
where rn = 1

Upvotes: 3

Thorsten Kettner
Thorsten Kettner

Reputation: 95052

You want one result row per type. This translates to GROUP BY type in SQL.

Then you say you don't care which of its IDs you get per type, so use MIN or MAX, it doesn't matter:

select list_type, min(id) as one_of_its_ids
from lists 
group by list_type
order by list_type;

Upvotes: 0

Clodoaldo Neto
Clodoaldo Neto

Reputation: 125434

select min(id) as id, list_type
from lists
group by list_type
order by list_type

Upvotes: 1

Related Questions