-2

How to find the fifth order for each customer and return title_order or null if the customer doesn't have the fifth order

Tables are

  • customer with columns Id, firstname, lastname...
  • order with columns order_id, title_order, id_custmer, date...

It can be done only with a query or do I need to create a function

Thanks in advance

marc_s
  • 732,580
  • 175
  • 1,330
  • 1,459
Mona
  • 1
  • *Fifth order* - **ordered by what**? You need to define what you want your orders to be ordered by, in order to count to the fifth order..... – marc_s Nov 16 '15 at 11:33
  • sorry order by date, the result that I want is first_name of customer and next fild returns the Title_order if the customer has the fifth order, thanks :), I have an answer below but can we do this in another way without using OUTER APPLY with OFFSET-FETCH, thanks – Mona Nov 16 '15 at 11:55

1 Answers1

0

You can use OUTER APPLY with OFFSET-FETCH:

select c.firstname, oa.title_order 
from customer c
outer apply(select title_order from order o 
            where o.id_custmer = c.Id 
            order by date 
            offset 4 ROW FETCH next 1 ROW only)oa
Giorgi Nakeuri
  • 35,155
  • 8
  • 47
  • 75
  • the result that I want is first_name of customer and next fild returns the Title_order if the customer has the fifth order, if not returns null thanks for answering – Mona Nov 16 '15 at 11:34
  • thanks it works :) , I didn't know the command that you apply – Mona Nov 16 '15 at 11:48
  • sorry again @Giorgi, is thene any way to solve this with function because I'm studing functions, I'm trying but I have not solve it yet (with function), thanks – Mona Nov 16 '15 at 13:51