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

maximus1974Asked:
Who is Participating?

[Product update] Infrastructure Analysis Tool is now available with Business Accounts.Learn More

x
I wear a lot of hats...

"The solutions and answers provided on Experts Exchange have been extremely helpful to me over the last few years. I wear a lot of hats - Developer, Database Administrator, Help Desk, etc., so I know a lot of things but not a lot about one thing. Experts Exchange gives me answers from people who do know a lot about one thing, in a easy to use platform." -Todd S.

Ryan ChongBusiness Systems Analyst , ex-Senior Application EngineerCommented:
tried "order by" descending order? and then just select the record with rn = 1

ROW_NUMBER() OVER(PARTITION BY wo_number ORDER BY first_name desc, last_name desc) rn

Experts Exchange Solution brought to you by

Your issues matter to us.

Facing a tech roadblock? Get the help and guidance you need from experienced professionals who care. Ask your question anytime, anywhere, with no hassle.

Start your 7-day free trial
David ToddSenior Database AdministratorCommented:
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
Russ SuterSenior Software DeveloperCommented:
Assuming the column [wo_number] is some kind of numeric column you can get the result you're after a lot more efficiently like this:
SELECT wo_number, first_name, last_name
    FROM ba_view_wo_labor
   WHERE wo_number IN (SELECT MAX(wo_number) FROM ba_view_wo_labor WHERE skill NOT LIKE '%INSPECTOR')

Open in new window

David ToddSenior Database AdministratorCommented:
Hi Russ,

I thought I'd like to compare your approach with mine - and I've used your one in the past.

I wrote the code for the Northwind database running on SQL 2017 and it looks like this:
use Northwind
go

-- David
select top 1
	e.EmployeeID
	, e.FirstName
	, e.LastName
from dbo.Employees e
order by
	e.EmployeeID desc
;

go

-- Russ
select 
	e.EmployeeID
	, e.FirstName
	, e.LastName
from dbo.Employees e
where
	e.EmployeeID in (
		select max( ee.EmployeeID )
		from dbo.Employees ee
		where
			1 = 1
			-- where condition
		)
;

go

Open in new window


The execution plans are identical with a cost of 0.0032832

In one sense performance is dependant on the exact relationship of the id column to the primary key and what its index is like imho.

Kind regards
  David
It's more than this solution.Get answers and train to solve all your tech problems - anytime, anywhere.Try it for free Edge Out The Competitionfor your dream job with proven skills and certifications.Get started today Stand Outas the employee with proven skills.Start learning today for free Move Your Career Forwardwith certification training in the latest technologies.Start your trial today
Query Syntax

From novice to tech pro — start learning today.