CODEWITHSUNDEEP
sqlpostgresql
Monu
Monu

Reputation: 47

Need help selecting distinct and max field in sql

So in the nc_sno.netcredit_dist_lp1 table, there are loan numbers and id numbers. Each loan number can have up to two different id numbers. I want to pull the highest id number for each loan number. This is what I have. Thanks for helping:

select
  tempkk3.loan_number
,tempkk3.username
, tempkk3.week_number
,lp.lp_rules_instruction as lp_instruction
,lp.insert_timestamp as time_entered_queue
,lp.id
from
  nc_sno.netcredit_dist_lp1 lp
join tempkk3 using (loan_number)

Upvotes: 0

Views: 30

Answers (1)

Gordon Linoff
Gordon Linoff

Reputation: 1269873

Postgres has the very nice distinct on functionality, which does exactly what you want:

select distinct on (loan_number) loan_number,
       tempkk3.username, tempkk3.week_number,
       lp.lp_rules_instruction as lp_instruction,
       lp.insert_timestamp as time_entered_queue,
       lp.id
from nc_sno.netcredit_dist_lp1 lp join
     tempkk3
     using (loan_number)
order by loan_number, id desc;

Upvotes: 1

Related Questions