postgresql - Pagination/orderby with multiple tables' columns

Question

Paging and sort by columns in 2 tables.

customer(id, first_name, last_name)
customer_email(customer_id, type, email).

One customer could have multiple emails.

Requirement: get 10 customers and sort by last_name, email.

--Sort by customer.last_name, customer_email.email

select c.*, ce.email
from customer c
inner join (select   distinct c.id, c.last_name
            from     customer c, customer_email ce 
            where    c.id = ce.customer_id 
            order by c.last_name, ce.email offset 0 limit 10
           ) AS paged_c on paged_c.id = c.id 
inner join customer_email ce on c.id = ce.customer_id
order by c.last_name, ce.email;

Doesn't work with the error

ERROR:  for SELECT DISTINCT, ORDER BY expressions must appear in select list
LINE 6:                 order by c.last_name, ce.email offset 0 limi...
                                              ^

Describe what you are trying to do. It is not clear what output you are expecting. — Nick
– Nick, Commented Sep 14, 2018 at 19:04

Laurenz Albe · Accepted Answer · 2018-09-18 04:21:28Z

1

PostgreSQL is right to complain about your query; your requirements are unclear.

If you want the first 10 customers, ordered by last name an e-mail, but each customer can have several e-mail addresses, how do you do that? Which of the e-mail addresses is used for sorting the customers?

The error message basically reflects that uncertainty.

Come up with a clear definition of the sort order and translate that into SQL, then PostgreSQL will be happy to run your query.

answered Sep 18, 2018 at 4:21

Laurenz Albe

257k22 gold badges312 silver badges388 bronze badges

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

Collectives™ on Stack Overflow

postgresql - Pagination/orderby with multiple tables' columns

1 Answer 1

Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

Comments

Your Answer

Sign up or log in

Post as a guest

Related