Need Correct Query Code Please? Thx. merry Christmas SQL Kids

Question

Post reply

Need Correct Query Code Please? Thx. merry Christmas SQL Kids

CoolKid40

SSC Enthusiast

Points: 186
More actions
December 23, 2019 at 7:25 pm

#3710103

Compose a SQL query to retrieve all products with its most recent two orders? Products that haven’t been ordered should be listed also. Use ERD below. Whats Correct Query Code?

Viewing 10 posts - 1 through 9 (of 9 total)

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

drew.allen SSC Guru Points: 76998 More actions · Answer 1

You'll learn a lot more if you do your own homework.

Drew

J. Drew Allen
Business Intelligence Analyst
Philadelphia, PA

Mike01 SSChampion Points: 11412 More actions · Answer 2

it would be nice to at least see what you've tried so far.

For better, quicker answers, click on the following...
http://www.sqlservercentral.com/articles/Best+Practices/61537/

For better answers on performance questions, click on the following...
http://www.sqlservercentral.com/articles/SQLServerCentral/66909/

Jonathan AC Roberts SSCoach Points: 18377 More actions · Answer 3

SELECT p.ID PRODUCT_ID, 
       p.PRODUCT_NAME, 
       o.ID ORD_ID, 
       o.DATE_ORDERED
  FROM dbo.S_PRODUCT p
 OUTER APPLY(SELECT TOP(2) 
                    o.ID
                    o.DATE_ORDERED
               FROM dbo.S_ITEM i
              INNER JOIN dbo.S_ORD o
                      ON o.ID = i.ORD_ID 
              WHERE i.PRODUCT_ID = p.ID
              ORDER BY o.DATE_ORDERED DESC) o

Thom A SSC Guru Points: 99134 More actions · Answer 4

Jonathan AC Roberts wrote:

SELECT p.ID PRODUCT_ID, 
       p.PRODUCT_NAME, 
       o.ID ORD_ID, 
       o.DATE_ORDERED
  FROM dbo.S_PRODUCT p
 OUTER APPLY(SELECT TOP(2) 
                    o.ID
                    o.DATE_ORDERED
               FROM dbo.S_ITEM i
              INNER JOIN dbo.S_ORD o
                      ON o.ID = i.ORD_ID 
              WHERE i.PRODUCT_ID = p.ID
              ORDER BY o.DATE_ORDERED DESC) o

Although I believe in Christmas spirit, I don't think giving them the answer is really going to help them in the long run. :/

Thom~

Excuse my typos and sometimes awful grammar. My fingers work faster than my brain does.
Larnu.uk

Jonathan AC Roberts SSCoach Points: 18377 More actions · Answer 5

Thom A wrote:

Although I believe in Christmas spirit, I don't think giving them the answer is really going to help them in the long run. :/

I don't think you can come up with that answer very easily if you are a beginner. It's sometimes best to see an actual answer, then see out how it works, than just being stuck on a problem getting nowhere.

Jeff Moden SSC Guru Points: 1004436 More actions · Answer 6

Jonathan AC Roberts wrote:

Thom A wrote:
Although I believe in Christmas spirit, I don't think giving them the answer is really going to help them in the long run. :/
I don't think you can come up with that answer very easily if you are a beginner. It's sometimes best to see an actual answer, then seeing out how it works, than just being stuck on a problem getting nowhere.

Nice formatting, as well, Jonathan!

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

CoolKid40 SSC Enthusiast Points: 186 More actions · Answer 7

Thanks for feedback responses everyone and yes its better to work backwards from answer and possibly explain your code syntax logic. I just learning sql. Did not know what sql was until 2019.

Jeff Moden SSC Guru Points: 1004436 More actions · Answer 8

CoolKid40 wrote:

Thanks for feedback responses everyone and yes its better to work backwards from answer and possibly explain your code syntax logic. I just learning sql. Did not know what sql was until 2019.

With that thought in mind, have you figured out what the Outer Apply in the code is doing for you and why Jonathan didn't use CROSS APPLY, instead?

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

webrunner SSC-Dedicated Points: 31892 More actions · Answer 9

CoolKid40 wrote:

Thanks for feedback responses everyone and yes its better to work backwards from answer and possibly explain your code syntax logic. I just learning sql. Did not know what sql was until 2019.

See also these diagrams, which you can use to find your own answers or to better understand any answers others give you.

https://stevestedman.com/2015/05/tsql-join-types-poster-version-4-1/

-- webrunner

-------------------
A SQL query walks into a bar and sees two tables. He walks up to them and asks, "Can I join you?"
Ref.: http://tkyte.blogspot.com/2009/02/sql-joke.html