Using DISTINCT and RANDOM with two Tables in PostgreSQL

Question

I have this problem. I need to query ticket number from tickettable and member name from memberstable, with random function and distinct keyword in postgres. The MemberName should be unique.

this code works in mySQL but doesn't work in PostgreSQL.

SELECT a.ticketNum, b.memberName 
FROM tickettable a, memberstable b 
GROUP BY memberName 
ORDER BY by rand()  
LIMIT 2

What should I do?

What error or output you are getting? Is there any common column for given tables? — Bhushan Dhande
– Bhushan Dhande, Commented May 19, 2014 at 11:01

Gordon Linoff · Accepted Answer · 2014-05-19 11:05:11Z

1

The random number function in Postgres is random(). Second, the group by is not going to work because ticketNum is not included. You can do what you want in several ways. Here is a correlated subquery way:

SELECT m.memberName,
       (select t.ticketNum
        from tickettable t
        order by random()
        limit 1
       ) as ticketNum
FROM memberstable m
LIMIT 2;

In addition, note the following:

The , in the from clause is replaced with cross join. Avoid commas in the from clause. Always use explicit join syntax.
The table aliases are abbreviates for the tables names, making the query easier to follow.

answered May 19, 2014 at 11:05

Gordon Linoff

1.3m62 gold badges706 silver badges857 bronze badges

Sign up to request clarification or add additional context in comments.

2 Comments

jedion Over a year ago

Thank you Very much sir Gordon Linoff.! The Code works. However, my desired result, didn't come up. The results in the ticketNum isn't distinct. And If I put DISTINCT before t.ticketNum an error occurs --- "[Err] ERROR: for SELECT DISTINCT, ORDER BY expressions must appear in select list" ... what should I do? thank you :)

Gordon Linoff Over a year ago

@JedAchas . . . Ask another question. This one does not specify that the ticket numbers should be unique, only that they should be random. That is a major change to the approach to the solution.

Collectives™ on Stack Overflow

Using DISTINCT and RANDOM with two Tables in PostgreSQL

1 Answer 1

2 Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

2 Comments

Your Answer

Sign up or log in

Post as a guest

Related