How to return the last row using ROW_COUNT

maximus1974
maximus1974 used Ask the Experts™
on
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

Comment
Watch Question

Do more with

Expert Office
EXPERT OFFICE® is a registered trademark of EXPERTS EXCHANGE®
View Solutions Only
Software Team Lead
Commented:
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
David ToddSenior Database Administrator

Commented:
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 Developer
Commented:
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 Administrator
Commented:
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

Do more with

Expert Office
Submit tech questions to Ask the Experts™ at any time to receive solutions, advice, and new ideas from leading industry professionals.

Start 7-Day Free Trial