Need to get records based on Sub Query

Question

Need to get records based on Sub Query

Smash125

Hall of Fame

Points: 3562
More actions
June 29, 2021 at 10:07 am

#3901566

I have written the query using ranking function. I tried to write the same query using sub query no clue. Can any body help
The query below as follows
WITH HighRecords AS
(
SELECT
BusinessEntityID,
FirstName,
MiddleName,
LastName,
TotalDue,
ROW_NUMBER() OVER(PARTITION BY BusinessEntityId ORDER BY TotalDue ASC) AS RN
FROM Person.Person Pp
INNER JOIN Sales.Customer Sc
ON Sc.PersonID=Pp.BusinessEntityID
INNER JOIN Sales.SalesOrderHeader Ss
ON Sc.CustomerID=Ss.CustomerID
)
SELECT BusinessEntityID,
FirstName,
MiddleName,
LastName,
TotalDue FROM HighRecords
ORDER BY BusinessEntityID ASC,TotalDue ASC

Viewing 6 posts - 1 through 5 (of 5 total)

You must be logged in to reply to this topic. Login to reply

Phil Parkin SSC Guru Points: 246960 More actions · Answer 1

The query you have provided contains both a subquery and a ranking function, so I don't understand what you are asking for.

The absence of evidence is not evidence of absence
- Martin Rees
The absence of consumable DDL, sample data and desired results is, however, evidence of the absence of my response
- Phil Parkin

Jonathan AC Roberts SSCoach Points: 18354 More actions · Answer 2

SELECT BusinessEntityID, 
       FirstName, 
       MiddleName, 
       LastName, 
       TotalDue
  FROM (SELECT BusinessEntityID, 
               FirstName, 
               MiddleName, 
               LastName, 
               TotalDue, 
               ROW_NUMBER() OVER(PARTITION BY BusinessEntityId ORDER BY TotalDue ASC) AS RN
          FROM Person.Person Pp
         INNER JOIN Sales.Customer Sc 
                 ON Sc.PersonID = Pp.BusinessEntityID
         INNER JOIN Sales.SalesOrderHeader Ss 
                 ON Sc.CustomerID = Ss.CustomerID) AS HighRecords
 ORDER BY BusinessEntityID ASC, TotalDue ASC

Smash125 Hall of Fame Points: 3562 More actions · Answer 3

Can this be achieved without using ranking function

Jonathan AC Roberts SSCoach Points: 18354 More actions · Answer 4

Smash125 wrote:

Can this be achieved without using ranking function

Yes, the ROW_NUMBER() function is unused so does nothing.

Jeff Moden SSC Guru Points: 1004323 More actions · Answer 5

What are you trying to do??? Get just the rows with the highest TotalDue for each BusinessEntityId ??? I so, just change the ASC in your ROW_NUMBER() to DESC and add a WHERE RN = 1 to your original query.

--Jeff Moden

RBAR is pronounced "ree-bar" and is a "Modenism" for Row-By-Agonizing-Row.
First step towards the paradigm shift of writing Set Based code:
________Stop thinking about what you want to do to a ROW... think, instead, of what you want to do to a COLUMN.

Change is inevitable... Change for the better is not.

Helpful Links:
How to post code problems
How to Post Performance Problems
Create a Tally Function (fnTally)