The Last Order

Question

The Last Order

Steve Jones - SSC Editor

SSC Guru

Points: 736172
More actions
June 6, 2016 at 10:28 pm

#327347

Comments posted to this topic are about the item The Last Order

Viewing 8 posts - 1 through 7 (of 7 total)

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

Thomas Franz Hall of Fame Points: 3893 More actions · Answer 1

I'd say, that - depending on which index it uses and / which other sorts it has to do / when you are MERGE joining / going parallel / are using partitions - it could return 43793 too for every row, because ORDER BY order_date is not UNIQUE and you forgot to add the OrderID to the ORDER BY clause.

Edit: I just realized, that I mixed up the dates and did not realize, that one is 2005 and the other one 2007. Regardless - if a customer had two orders at the same day my statement above would still true (no guaranted order)

God is real, unless declared integer.

Cary Hower-563110 Say Hey Kid Points: 662 More actions · Answer 2

I agree that 57418 (from the most current order) will be returned for each row and that all three rows will be returned. However, the list of answers to choose from was a little misleading. The correct answer was stated as the last order will be returned. I believe that all orders will be returned, not just the last. The answer that most fully explained this result to me was The current order is returned for each row (SalesOrderID = TD), as this is the LAST_VALUE for that row in the partition

George Vobr SSCrazy Eights Points: 9996 More actions · Answer 3

Thanks Steve for an interesting question and precise explanation. Funny thing is that the answer was easy to find in the article "Aunt" Kathi Kellenberger in SSC Newsletter last Friday.

TomThomson SSC Guru Points: 104773 More actions · Answer 4

Good question.

After a hard one yesterday to start the week and an ambiguous (to say the least) one to end last week it was a nice change to get an easy and unambiguous one today.

Tom

Ed Wagner SSC Guru Points: 287026 More actions · Answer 5

I also thought it was a good question and I really like the windowing functions.

Tally Tables - Performance Personified
String Splitting with True Performance
Best practices on how to ask questions

Steve Jones - SSC Editor SSC Guru Points: 736172 More actions · Answer 6

Cary Hower-563110 (6/7/2016)
I agree that 57418 (from the most current order) will be returned for each row and that all three rows will be returned. However, the list of answers to choose from was a little misleading. The correct answer was stated as the last order will be returned. I believe that all orders will be returned, not just the last. The answer that most fully explained this result to me was The current order is returned for each row (SalesOrderID = TD), as this is the LAST_VALUE for that row in the partition

It asks about the TD column. In that column, the last order in the partition/frame is returned.

Raghavendra Mudugal SSChampion Points: 10658 More actions · Answer 7

Thank you, Steve, good one.

(Well, it took less then 30 seconds for me to read and select the right choice- either my mind did malfunctioned or I just became smarter...hmm let me see how am I going to answer tomorrow's QTOD 😉 )

ww; Raghu
--
The first and the hardest SQL statement I have wrote- "select * from customers" - and I was happy and felt smart.