0

I have a table of sales belonging to various customers.

A customer can have more than one sale.

How do I figure out the nth sale for each customer/sale?

I am using Laravel but a plain MySQL answer would be just as good.

My query builder is:

$q = DB::table('sales AS S')
        ->select(DB::raw('CONCAT("row_", S.id) AS DT_RowId'),
            'S.id', 'S.created_at', 'users.name AS agent',
            DB::raw('CONCAT(C.first_name, " ", COALESCE(C.last_name, "")) AS name'), 'S.net',
            DB::raw('(S.net - P.base_buy_price) AS margin'),
            DB::raw('((S.net - P.base_buy_price)/P.base_price * 100) AS margin_percent'))
        ->join('users', 'users.id', '=', 'S.user_id')
        ->join('customers AS C', 'C.id', '=', 'S.customer_id')
        ->join('products AS P', 'P.id', '=', 'S.product_id')
        ->get();

Clarification

Lets say customer A has bought three times, on dates 2015-06-12, 2015-06-17 and 2016-01-17.

In the results I want each one of those rows telling me which sale is the nth i.e.:

2015-06-12 - 1st sale

2015-06-17 - 2nd sale

2016-01-17 - 3rd sale

...
DB::raw('(SELECT COUNT(*) FROM sales WHERE sales.created_at <= S.created_at AND sales.customer_id = C.id) AS nth')
...

Seems very inefficient?

imperium2335
  • 23,402
  • 38
  • 111
  • 190
  • 3
    `LIMIT n, 1` is that what you want? – CodeBird Jan 19 '16 at 20:00
  • 2
    Already answered: http://stackoverflow.com/questions/10457458/select-specific-row-from-mysql-table. But @CodeBird is correct. Google is your friend. – Mr Rho Jan 19 '16 at 20:02
  • @CodeBird I was thinking it would be some kind of subquery that orders the sales for each customer by date? – imperium2335 Jan 19 '16 at 20:02
  • @imperium2335 I don't know what exactly you want, but `LIMIT n, 1` will get you the nth record, you can order by whatever you want... Maybe clarify your question a bit more... – CodeBird Jan 19 '16 at 20:04
  • You can still order the query - but that is dependent on your table structure and where the sale date is located. Just add some form of ORDER BY and then use a limit function if available - not all db's sql variants have it however. Not familiar with Laravel. – Mr Rho Jan 19 '16 at 20:04
  • Please see my edit. The suggested answer is not what I need btw. – imperium2335 Jan 19 '16 at 20:08
  • @Barmar please see my edit to see how it is different. – imperium2335 Jan 19 '16 at 20:20
  • That's not getting the Nth row, it's getting the row number for each row. – Barmar Jan 19 '16 at 20:23
  • What you want is the rank, see http://stackoverflow.com/questions/3333665/mysql-rank-function/3333697#3333697 – Barmar Jan 19 '16 at 20:23

0 Answers0