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
GBTISAsked:
Who is Participating?
 
slightwv (䄆 Netminder) Commented:
select pers, nbr, idnbr, datelastmaint from
(
select pers, nbr, idnbr, datelastmaint, row_number() over(partition by pers order by idnbr desc) myrownum from table
) where myrownum=1
0
 
sdstuberCommented:
s elect persnbr,max(idnbr) from yourtable
group by persnbr
0
 
Richard QuadlingSenior Software DeveloperCommented:
SELECT
	PersNbr,
	MAX(IDNbr) MaxIDNbr
FROM
	table
GROUP BY
	PersNbr

Open in new window


will return the highest IDNbr per PersNbr.

What do you want to do with the DateLastMaint?

Do you have an ID column?
0
Ultimate Tool Kit for Technology Solution Provider

Broken down into practical pointers and step-by-step instructions, the IT Service Excellence Tool Kit delivers expert advice for technology solution providers. Get your free copy now.

 
GBTISAuthor Commented:
slightwv,

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'
0
 
slightwv (䄆 Netminder) Commented:
>>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.
0
 
GBTISAuthor Commented:
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.
0
 
slightwv (䄆 Netminder) Commented:
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
0
 
GBTISAuthor Commented:
I was able to get that to work.  Thank you very much.
0
 
slightwv (䄆 Netminder) Commented:
Glad to help.
0
Question has a verified solution.

Are you are experiencing a similar issue? Get a personalized answer when you ask a related question.

Have a better answer? Share it in a comment.

All Courses

From novice to tech pro — start learning today.