Avatar of maximus1974
maximus1974

asked on 

How to return the last row using ROW_COUNT

How can I have the following statement below return the last or highest row for rn?

SELECT wo_number, first_name, last_name
    FROM (SELECT wo_number,
                 first_name,
                 last_name,
                 ROW_NUMBER() OVER(PARTITION BY wo_number ORDER BY first_name, last_name) rn,
                 COUNT(*) OVER (PARTITION BY wo_number) cnt
            FROM ba_view_wo_labor
           WHERE skill NOT LIKE '%INSPECTOR')
   WHERE cnt > 1 AND rn = 1
ORDER BY wo_number

Open in new window

Oracle Database* PL/SQLSQL

Avatar of undefined
Last Comment
David Todd
ASKER CERTIFIED SOLUTION
Avatar of Ryan Chong
Ryan Chong
Flag of Singapore image

Blurred text
THIS SOLUTION IS ONLY AVAILABLE TO MEMBERS.
View this solution by signing up for a free trial.
Members can start a 7-Day free trial and enjoy unlimited access to the platform.
See Pricing Options
Start Free Trial
Avatar of David Todd
David Todd
Flag of New Zealand image

Hi,

try this:
select top 1 l.wo_number, l.first_name, l.last_name
from dbo.ba_view_we_labour l
where
	l.skill not like '%inspector'
order by
	l.wo_number desc
;

Open in new window

Regards
  David


PS Not going to perform very well on larger datasets
SOLUTION
Avatar of Russ Suter
Russ Suter

Blurred text
THIS SOLUTION IS ONLY AVAILABLE TO MEMBERS.
View this solution by signing up for a free trial.
Members can start a 7-Day free trial and enjoy unlimited access to the platform.
SOLUTION
Avatar of David Todd
David Todd
Flag of New Zealand image

Blurred text
THIS SOLUTION IS ONLY AVAILABLE TO MEMBERS.
View this solution by signing up for a free trial.
Members can start a 7-Day free trial and enjoy unlimited access to the platform.
Oracle Database
Oracle Database

Oracle is an object-relational database management system. It supports a large number of languages and application development frameworks. Its primary languages are SQL, PL/SQL and Java, but it also includes support for C and C++. Oracle also has its own enterprise modules and application server software.

81K
Questions
--
Followers
--
Top Experts
Get a personalized solution from industry experts
Ask the experts
Read over 600 more reviews

TRUSTED BY

IBM logoIntel logoMicrosoft logoUbisoft logoSAP logo
Qualcomm logoCitrix Systems logoWorkday logoErnst & Young logo
High performer badgeUsers love us badge
LinkedIn logoFacebook logoX logoInstagram logoTikTok logoYouTube logo