CODEWITHSUNDEEP
sqlpostgresqldistinct
Max
Max

Reputation: 372

Select distinct name with random id

I have a table with an id and a name (an a bunch of other stuff not relevant for this query). Now I need an SQL statement that returns one row per distinct name and in that row I need the name and one id (can be any id).

The table is looking something like this: 

id | name
---+-----
 1 | a2
 2 | a2
 3 | a4
 4 | a4
 5 | a2
 6 | a3

btw. using Postgres 8.4

Tried various combinations of grouping or joining with self. Is this even possible without creating extra tables?

Upvotes: 2

Views: 1887

Answers (3)

Dewasish Mitruka
Dewasish Mitruka

Reputation: 2896

select ID, name from table group by name;

Upvotes: 0

Jomoos
Jomoos

Reputation: 13083

You may look at PostgreSQL wiki. It shows how to select random rows.

You may use random() function to select random rows using ORDER BY clause of SELECT. Example:

SELECT id FROM mytable ORDER BY random()

You can then use GROUP BY to select distinct names. You may need to limit results using LIMIT clause. So the query looks something like this:

SELECT id, name FROM table_name GROUP BY name ORDER BY random() LIMIT 1

Upvotes: 0

Joe Stefanelli
Joe Stefanelli

Reputation: 135818

Arbitrarily choosing to return the minimum id per name.

SELECT name, MIN(id)
    FROM YourTable
    GROUP BY name

Upvotes: 4

Related Questions