mardi 20 juin 2017

Selecting one random data from a column from multiple rows in oracle

I am creating a view that needs to select only one random row for each customer. Something like:

select c.name, p.number
from customers c, phone_numbers p
where p.customer_id = c.id

If this query returns:

  • Customer1 1
  • Customer1 2
  • Customer1 3
  • Customer2 4
  • Customer2 5
  • Customer3 6

I need it to be something like:

  • Customer1 1
  • Customer2 4
  • Customer3 6

Rownum wont work because it will select only the first from all 6 records, and i need the first from each customer. I need solution that won't affect performance much, because the query that selects the data is pretty complex, this is just an example to explain what I need. Thanks in advance.




Aucun commentaire:

Enregistrer un commentaire