I am calculating a field called “Degree Level” in a view. This is a field in the table “Degrees”, and the table shows degrees for each faculty member. A faculty member can have more than one degree. The field “degree level” is also in the table “Crosswalk_Table”…
Tag: max
MAX() OVER PARTITION BY not working as intended
I’m having some issues when i try to obtain the MAX value of a field withing a set of records and i hope some of you can help me finding what am i doing wrong. I’m trying to get the ID of the item of the most expensive line, within an order. Given this query: I’m getting this results: Result…
How to select the most recent date for a specific condition in another column in Postgres?
I have a table with a structure like so. I’m trying to create a column with a query called most recent call which has the most recent call date for each id. Like this: I just want to have the most recent date for each id out of all the rows where the subject column equals call Answer You can
Maximum mark in Subject with Staff name
Write a query to display list of staff name, subject name handled, maximum mark scored in that subject. Give an alias to the maximum mark as max_mark. Sort the result based on maximum mark in descending. This is the schema diagram. This is the code I have tried. I was abled to pass one testcase but I could no…
mysql update with if and max
I have lost hours on this and nothing works for me. I have filed strp_aa that is default NULL. The filed strp_aa should update only if its null with MAX strp_aa + 1, and if its not null if it already has a number it should stay the same. short version of code is Also tired I tried multiple things
Codeigniter MySQL select max using varchar type value
I want to select max auto_no from the following varchar type column in my codeigniter model +————+ | auto_no | +————+ | 2020-00821 | | 2020-00822 | | 2020-00823 | | 2020-00824 | | …
SQL selecting most recent row inside join
I have 2 tables companies and invoices I want to select all companies with their most recent invoice price. I don’t seem to get it working. This is what I tried: But the query loads very long and I don’t know why. The structure looks like this: companies invoices The BC_ID is the same as the compa…
Easy substraction of year’s values
I do have the following database table containing the timestamps in unix format as well as the total yield (summing up) of my solar panels every 5 mins: Now I would like to calculate how much energy was produced each year. I thought of reading the first and last timestamp using UNION of each year: This works …
SQL – joining 3 tables and choosing newest logged entry per id
I got rather complicated riddle to solve. So far I’m unlocky. I got 3 tables which I need to join to get the result. Most important is that I need highest h_id per p_id. h_id is uniqe entry in …
How do I find the 2nd most recent order of a customer
I have the customers first order date and last order date by doing MIN and MAX on the created_at field (grouping by email), but I also need to get the customers 2nd most recent order date (the order date right before the last orderdate ) Answer Use window function ROW_NUMBER() (available in MySQL 8.0): This w…