GBTIS
asked on
SQL script to return only one row per persnbr
I am trying to put together a script to return only the highest numbered IDNbr from the data below for each person. How would I script that? Thanks.
PersNbr IDNbr DateLastMaint
1 1 8/1/2011
1 2 8/20/2011
1 3 9/5/2011
2 1 8/15/2011
2 1 8/18/2011
3 1 9/1/2011
4 1 8/5/2011
4 2 9/12/2011
PersNbr IDNbr DateLastMaint
1 1 8/1/2011
1 2 8/20/2011
1 3 9/5/2011
2 1 8/15/2011
2 1 8/18/2011
3 1 9/1/2011
4 1 8/5/2011
4 2 9/12/2011
ASKER CERTIFIED SOLUTION
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
SOLUTION
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
SOLUTION
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
>>Exisitng Query to Combine With
Where is idnbr in that query?
Anyway you should just be able to take what you have and add the row_number function call using the proper columns in the query.
Where is idnbr in that query?
Anyway you should just be able to take what you have and add the row_number function call using the proper columns in the query.
ASKER
The idnbr would be in the cardissue (d) table. I don't really need it in the output I just want the record with the highest value.
Untested (just typed in):
select cardnbr, accountnbr, persnbr, currentstatus, accountstatus, office, phone from (
select a.cardnbr, b.accountnbr, c.persnbr, d.currentstatus, e.accountstatus, e.office, e.phone, row_number() over(partition by c.persnbr order by d.idnbr) myrownum
from card a, cardpers b, pers c, cardissue d, account e
where a.cardnbr=b.cardnbr and b.persnbr = c.persnbr and b.cardnbr=d.cardnr and b.accountnbr=e.accountnbr and b.cardtype<>'CARD'
)
where myrownum=1
select cardnbr, accountnbr, persnbr, currentstatus, accountstatus, office, phone from (
select a.cardnbr, b.accountnbr, c.persnbr, d.currentstatus, e.accountstatus, e.office, e.phone, row_number() over(partition by c.persnbr order by d.idnbr) myrownum
from card a, cardpers b, pers c, cardissue d, account e
where a.cardnbr=b.cardnbr and b.persnbr = c.persnbr and b.cardnbr=d.cardnr and b.accountnbr=e.accountnbr and b.cardtype<>'CARD'
)
where myrownum=1
ASKER
I was able to get that to work. Thank you very much.
Glad to help.
ASKER
Your solution seems to work the best seeing as though I have multiple columns from multiple tables. Can you help me get your query into my existing query. Basically it goes like this.
Your Query Modified:
select cardnbr, currentstatus, idnbr, datelastmaint from
(
select cardnbr, currentstatus, idnbr, datelastmain, row_number() over(partition by cardnbr order by idnbr desc) myrownum from cardissue
) where myrownum=1
Exisitng Query to Combine With
select a.cardnbr, b.accountnbr, c.persnbr, d.currentstatus, e.accountstatus, e.office, e.phone
from card a, cardpers b, pers c, cardissue d, account e
where a.cardnbr=b.cardnbr and b.persnbr = c.persnbr and b.cardnbr=d.cardnr and b.accountnbr=e.accountnbr and b.cardtype<>'CARD'