CODEWITHSUNDEEP
sqlgreatest-n-per-group
RTR
RTR

Reputation: 129

Return Max ID SQL

I have a table with several rows as mentioned below having same account id :

id  account_id  id_intern    Name   Active   mobile_no    landline_no     email

1    0011abs      66654         A     yes     098937888    098937888     [email protected]  
2    0011abs      66655         B     yes     098937666    098937666     [email protected] 
3    0011abs      66656         C     no      098937777    098937777     [email protected] 
4    0011abs      66657         D     yes                  098937666     [email protected] 
5    0011abs      66658         E     yes     098937111                  [email protected] 
6    0011abs      66659         F     yes     098937111    098937665

I am searching for script that can return me just one line for all the common account_id present in the table with the following condition:

For an account_id with several id_intern:
consider those lines with the status active 'yes',
then those lines with entered mobile_no (not empty),
then those lines with entered landline_no (not empty),
then those lines with entered email (not empty),
then if still we have several lines (in this case for users with name A and B)
then we will consider the line with max id_intern.

Expected Result :

id   account_id  id_intern    Name   active     mobile_no     landline_no   email
2    0011abs      66655         B     yes       098937666    098937666     [email protected]

I tried the script but i am unable to fulfil these conditions :(

Thanks in advance for your help.

Upvotes: 0

Views: 53

Answers (1)

forpas
forpas

Reputation: 164069

Use row_number() window function:

select id, account_id, id_intern, Name, Active, mobile_no, landline_no, email
from (
  select *, row_number() over (partition by account_id order by id_intern desc) rn
  from tablename
  where Active = 'yes' 
    and mobile_no is not null and landline_no is not null and email is not null
) t    
where rn = 1

If you want 1 row for each account_id even if not all the conditions are satisfied, the you must use a conditional ORDER BY clause:

select id, account_id, id_intern, Name, Active, mobile_no, landline_no, email
from (
  select *, 
    row_number() over (
      partition by account_id 
      order by
        case when Active = 'yes' then 1 else 2 end,
        case when mobile_no is not null then 1 else 2 end,
        case when landline_no is not null then 1 else 2 end,
        case when email is not null then 1 else 2 end,
        id_intern desc
    ) rn
  from tablename
) t    
where rn = 1

Upvotes: 2

Related Questions