How to select only first rows that satisfies conditions?

Question

I'm doing a join between two tables and adding a condition want to obtain only the first row that satisfie the join condition and the "extern" condition too.

This query for example:

select * from PRMPROFILE p, user v
where 
p.id = v.profile
and p.language = 0
and v.userid like '%TEST%';

First of all, i want to know how to group the result of this inner join using the profile (v.profile or p.id). After that how to show only the first appearence for each group.

Thanks in advance.

Answer 1

You can use an analytic query for this:

select *
from (
    select p.*, v.*,
        row_number() over (partition by p.id order by v.userid) as rn
    from prmprofile p
    join user v on v.profile = p.id
    where p.language = 0
    and v.userid like '%TEST%'
)
where rn = 1;

The inner query gets all the data (but using * isn't ideal), and adds an extra column that assigns a row number sequence across each p.id value. They have to be ordered by something, and you haven't said what makes a particular row 'first', so I've guessed as user ID - you can of course change that to something more appropriate, that will give consistent results when the query is rerun. (You can look at rank and dense_rank for alternative methods to pick the 'first' row).

The outer query just restricts the result set to those where the extra column has the value 1, which will give you one row for every p.id.

Another approach would be to use a subquery to identify the 'first' row and join to that, but it isn't clear what the criteria would be and if it would be selective enough.

Answer 2

Please try:

select * from(
  select *, 
    row_number() over (partition by v.userid order by v.userid) RNum
  from PRMPROFILE p, user v
  where 
  p.id = v.profile
  and p.language = 0
  and v.userid like '%TEST%'
  )x 
where RNum=1;