Jessiedillongladys's Profile

75
Points

Questions
13

Answers
13

  • Asked on August 30, 2020 in Sql.

    You could use a subquery, then greatest() (if your database supports it):

    select      t.*,     case greatest(score0, score1, score2)         when score0 then 0         when score1 then 1         when score2 then 2     end action_value from (     select         user_id,         time_id,         max(case when actual_value = 0 then score end) score0,         max(case when actual_value = 1 then score end) score1,         max(case when actual_value = 2 then score end) score2     from mytable     group by user_id, time_id ) t 
    • 9 views
    • 1 answers
    • 0 votes
  • Asked on August 30, 2020 in Sql.

    SQL tables represent unordered sets. There is no ordering — and no cumulative max — unless you have a column that specifies the ordering.

    This is easy to include by having an identity column. Such a column will preserve insertion order, which is apparently what you want. So, this does what you want:

    DECLARE @trial TABLE (id int identity, val int)  INSERT INTO @trial (val) VALUES (1), (0),(0), (0), (0),(0), (7), (0),(9), (0), (0),(12), (0), (14),(0)   select * from @trial  select max(val) over (order by id) as running_max from @trial order by id; 

    EDIT:

    In your sample data, you want to look forward as well as backward. For this, you can use a case expression to find the cases where the first rows have 0:

    select (case when max(val) over (order by id) = 0              then min(case when val > 0 then val end) over (order by id desc)              else max(val) over (order by id)          end) as running_max from trial order by id; 

    Here is a db<>fiddle.

    • 6 views
    • 2 answers
    • 0 votes
  • Try the SYS_CONTEXT() function — it can return all sorts of interesting things, including the DB Name:

    select sys_context('USERENV', 'DB_NAME') from dual; 

    See the documentation for all the other values that can be used for the second parameter. This is a funciton that can be called both from a SQL query as above, or from PL/SQL.

    • 10 views
    • 1 answers
    • 0 votes
  • Asked on August 30, 2020 in Sql.

    You need to use namespace in controller and models

    Controller:

    namespace App\Http\Controllers;  use App\Post; 

    Model:

    namespace App; 

    A name space allows you to partition code into logical groups by defining them into their own "namespace". A text string after the "namespace" keyword identifies the name space and all code below it then lives within that name space. Name spaces also provide a way to group interfaces functions and constants.

    You can read more about namespace.

    Hope this works in your case!!

    • 8 views
    • 1 answers
    • 0 votes
  • Asked on August 30, 2020 in Sql.

    I think that’s joins and aggregation:

    select m.mid, m.title, p.gender, count(*) no_persons from movie m inner join m_cast mc on mc.mid = m.mid inner join person p on p.pid = mc.pid group by m.mid, m.title, p.gender 

    If all you want is the id of the movie (no other column from that table), then you don’t even need the movie table:

    select mc.mid, m.title, p.gender, count(*) no_persons from m_cast mc inner join person p on p.pid = mc.pid group by mc.move_id, p.gender 

    Finally, if you want the count of genres in columns rather than rows, you can use conditional aggregation. Assuming that gender has values 'male' and 'female':

    select      mc.mid,      m.title,      sum(case when p.gender = 'male'   then 1 else 0 end) no_persons_male     sum(case when p.gender = 'female' then 1 else 0 end) no_persons_female from m_cast mc inner join person p on p.pid = mc.pid group by mc.move_id 
    • 13 views
    • 1 answers
    • 0 votes
  • Asked on August 30, 2020 in Mysql.

    I don’t know if this will work as I don’t have you database, but why not join the tables together and group the related records based on the count.

    Something like.

    $work_orders = DB::table('work_orders')                 ->select(                    DB::raw('COUNT(work_order_tasks.work_order_id) count'),                     'work_orders.*',                     'vehicles.vin',                     'work_orders.transportation_type as transport',                     'clients.name as client_name',                     'events.event_name as event_name'                 )                 ->havingRaw('count>0')                 ->groupBy('work_orders.id')                 ->where('status', '!=', 'Closed')                 ->where("work_order_tasks.status", "<>", "Completed")                 ->leftJoin('vehicles', 'vehicles.id', '=', 'work_orders.gsm_vehicle_id')                 ->leftJoin('clients', 'clients.id', '=', 'work_orders.client_id')                 ->leftJoin('events', 'events.id', '=', 'work_orders.gsm_event_id')                 ->leftJoin('work_order_tasks', 'work_orders.id', '=','work_order_tasks.work_order_id')                 ->get(); 
    • 7 views
    • 1 answers
    • 0 votes
  • Asked on August 30, 2020 in Mysql.

    Try ordering by the leading digits of the sizes:

    select     size_categories.size_id,     GROUP_CONCAT(         sizes.name         ORDER BY CAST(REGEXP_SUBSTR(sizes.name, '^[0-9]+') AS UNSIGNED)     ) as size_name 

    This assumes you are using MySQL 8+. The above is the only easy way to do this which comes to mind, given that we don’t necessarily know how many digits the size might have, or which letters might be following the numeric size.

    • 9 views
    • 2 answers
    • 0 votes
  • Asked on August 30, 2020 in Mysql.

    Use row_number()

    select * from ( select t1_id, title,t2_id, edit_date, content,       row_number() over(partition by t1_id order by edit_date desc) as rn from table1 t inner join table2 t1 on t.t1_id=t1.t2_t1_id )A where rn=1 
    • 6 views
    • 2 answers
    • 0 votes
  • Asked on August 30, 2020 in Mysql.

    Single quotes should be used for string values like in the VALUES() list.

    Backticks are generally used to indicate an identifier and as well be safe from accidentally using the reserved keywords.

    In combination of PHP and MySQL, double quotes and single quotes make your query writing time so much easier.

    • 9 views
    • 12 answers
    • 0 votes
  • Asked on August 30, 2020 in Mysql.

    import your user.js model in config.js a example:-

         const User = require('enter your path here for user.js')       User.sync(); 
    • 4 views
    • 1 answers
    • 0 votes