join - how to get only the first row from the left table

Question

join - how to get only the first row from the left table

Viewing 5 posts - 16 through 19 (of 19 total)

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

Lynn Pettis SSC Guru Points: 442467 More actions · Answer 1

JackIsJack wrote:

I'll that in mind for later.
Currently, I am not dealing with such a configuration.
We have ~300 user max (not concurrent users, a total of 300 users), we don't have to sort records at application level.
I hope I'll never meet those constraints lol.

Always plan for user requirements to change. Users rarely know what they really want until you give them what they asked for.

Lynn Pettis SSC Guru Points: 442467 More actions · Answer 2

Jeff Moden wrote:

Lynn,
Thank you for the very kind words. If someone with your knowledge has claimed that I'm their mentor, then I'm truly humbled. To be honest, you're as much of a mentor to me as you claim I am to you. I've learned a huge amount from you and, like many mentors, you're not even aware of it. You set the bar pretty high, Mr. Pettis.
You were definitely on my bucket list of people important to me and I was tickled to death to have that come true at the Colorado Springs SQL Saturday. It was indeed a great pleasure to finally get to meet you and spend some time talking face-to-face with you after more than a decade of not being able to.
Hopefully, that won't be the last time.

And I think I surprised you again that August when I came to the Providence SQL Saturday. I took advantage of Kylie going to school at Johnson and Wales University when I learned you were going to present there as well. Kylie and I really appreciated you taking us to dinner after the event. I also surprised Ray Kim there. I would have introduced myself to him before his presentation but he seemed busy trying to get things working so he could start.

Jeff Moden SSC Guru Points: 1004340 More actions · Answer 3

ScottPletcher wrote:

An alternative is like below. Which performs better depends on total data volume and index(es) present:
Edit: I'm at work so I have only a limited time to review all posts, sorry if this was already posted and I missed it.
SELECT 
SOH.[SalesOrderID],
SOH.[DueDate],
SOH.[SalesOrderID]
, SOD.[OrderQty]
, SOD.[UnitPrice]
FROM 
[Sales].[SalesOrderHeader] SOH 
OUTER APPLY (
    SELECT TOP (1) *
    FROM [Sales].[SalesOrderDetail] SOD
WHERE SOH.[SalesOrderID] = SOD.[SalesOrderID]
    ORDER BY [whatever_column(s)_give_you_the_"first"_one_as_you_want_it]
) AS SOD

What I want to do is test your claim of performance and don't want to take a chance on buggering up your intended code. Can you post the code as you've actually tested it, please? If you tested with added indexes, please include those, as well.

--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)

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

Jeff Moden wrote:

ScottPletcher wrote:
An alternative is like below. Which performs better depends on total data volume and index(es) present:
Edit: I'm at work so I have only a limited time to review all posts, sorry if this was already posted and I missed it.
SELECT 
SOH.[SalesOrderID],
SOH.[DueDate],
SOH.[SalesOrderID]
, SOD.[OrderQty]
, SOD.[UnitPrice]
FROM 
[Sales].[SalesOrderHeader] SOH 
OUTER APPLY (
    SELECT TOP (1) *
    FROM [Sales].[SalesOrderDetail] SOD
WHERE SOH.[SalesOrderID] = SOD.[SalesOrderID]
    ORDER BY [whatever_column(s)_give_you_the_"first"_one_as_you_want_it]
) AS SOD
What I want to do is test your claim of performance and don't want to take a change on buggering up your intended code. Can you post the code as you've actually tested it, please?

I think this answer is different from the OP's request. This will only produce 1 row per [Sales].[SalesOrderHeader] row. The OP asked for only 1 [Sales].[SalesOrderHeader] row being shown (with the rest empty), and wanted all the LEFT JOINed rows shown:

JackIsJack wrote:

And here is the result I want instead :

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

Lynn Pettis wrote:

Jeff Moden wrote:
Lynn,
Thank you for the very kind words. If someone with your knowledge has claimed that I'm their mentor, then I'm truly humbled. To be honest, you're as much of a mentor to me as you claim I am to you. I've learned a huge amount from you and, like many mentors, you're not even aware of it. You set the bar pretty high, Mr. Pettis.
You were definitely on my bucket list of people important to me and I was tickled to death to have that come true at the Colorado Springs SQL Saturday. It was indeed a great pleasure to finally get to meet you and spend some time talking face-to-face with you after more than a decade of not being able to.
Hopefully, that won't be the last time.
And I think I surprised you again that August when I came to the Providence SQL Saturday. I took advantage of Kylie going to school at Johnson and Wales University when I learned you were going to present there as well. Kylie and I really appreciated you taking us to dinner after the event. I also surprised Ray Kim there. I would have introduced myself to him before his presentation but he seemed busy trying to get things working so he could start.

You did, indeed. That was a great night. It was a pleasure to visit with both you and your daughter.

--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)