[Webinar] Streamline your web hosting managementRegister Today

x
  • Status: Solved
  • Priority: Medium
  • Security: Public
  • Views: 344
  • Last Modified:

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
0
GBTIS
Asked:
GBTIS
3 Solutions
 
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
The new generation of project management tools

With monday.com’s project management tool, you can see what everyone on your team is working in a single glance. Its intuitive dashboards are customizable, so you can create systems that work for you.

 
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

Featured Post

The new generation of project management tools

With monday.com’s project management tool, you can see what everyone on your team is working in a single glance. Its intuitive dashboards are customizable, so you can create systems that work for you.

Tackle projects and never again get stuck behind a technical roadblock.
Join Now