CODEWITHSUNDEEP
sqloracle-databaseselectjoingroup-by
Jorge Vega Sánchez
Jorge Vega Sánchez

Reputation: 7590

How to select only first rows that satisfies conditions?

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.

Upvotes: 10

Views: 68325

Answers (5)

FrostNovaZzz
FrostNovaZzz

Reputation: 832

You can use LIMIT keyword.

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

Upvotes: 1

user10772902
user10772902

Reputation: 11

select * from PRMPROFILE p, user v
where p.id = v.profile and p.language = 0
and v.userid like '%TEST%'
fetch first 1 row only

Upvotes: 1

TechDo
TechDo

Reputation: 18629

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;

Upvotes: 3

Alex Poole
Alex Poole

Reputation: 191235

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.

Upvotes: 13

user1613212
user1613212

Reputation: 93

It will display only the top result

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

Upvotes: 0

Related Questions