CODEWITHSUNDEEP
databasepostgresqllaravel
Shriganesh Shintre
Shriganesh Shintre

Reputation: 2498

Laravel - query builder to select multiple rows with unique column value (which has max value from another column)

I have a table like this

data | usage_date | usage_hour
x    | 03/03/2016 | 05:30:30
y    | 03/02/2016 | 11:30:30
z    | 03/03/2016 | 07:30:30
p    | 03/02/2016 | 05:30:30

When I run Laravel query I would like see following rows being selected

y    | 03/02/2016 | 11:30:30
z    | 03/03/2016 | 07:30:30

So basically I want to build a query which will give unique values for 'usage_date', with max 'usage_hour'. How can I build this query?

Upvotes: 2

Views: 3290

Answers (2)

pumbo
pumbo

Reputation: 3826

First of all, you need to know how to do this in plain SQL, because Laravel's QueryBuilder is just a tool for building SQL-queries.

The task you described is kind of tricky and unfortunately there's no short and easy SQL query for that.

It can be done with window functions:

SELECT DISTINCT usage_date, 
    first_value(usage_hour) OVER (PARTITION BY usage_date ORDER BY usage_hour DESC) as usage_hour, 
    first_value(data) OVER (PARTITION BY usage_date ORDER BY usage_date DESC, usage_hour DESC) as data
FROM t

(sqlfiddle example)

With QueryBuilder it will look like this:

DB::table('t')
    ->distinct()
    ->select([
        'usage_date',
        DB::raw('first_value(usage_hour) OVER (PARTITION BY usage_date ORDER BY usage_hour DESC) as usage_hour'),
        DB::raw('first_value(data) OVER (PARTITION BY usage_date ORDER BY usage_date DESC, usage_hour DESC) as data')
    ])
    ->get()

Or it can be done with a subquery (which is inefficient and not cool):

SELECT usage_date, max(usage_hour) as usage_hour,
  (SELECT data FROM t AS t2 
   WHERE t2.usage_date = t.usage_date AND t2.usage_hour = max(t.usage_hour) 
  LIMIT 1) AS data
FROM t
GROUP BY usage_date

If anyone knows a method without subqueries and window functions, please let me know.

Upvotes: 1

oseintow
oseintow

Reputation: 7371

Try this and let see how it goes 

$table = \DB::table('table name')->distinct('usage_date')->groupBy('usage_date')->orderBy('usage_hour','desc')->get();

Upvotes: 0

Related Questions