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 …
Tag: sql
How do you filter with a Dataframe, list, vector etc. to a table in a database in R?
I have a large set of id-s which is in a csv file. How could I filter on a database table using only that one-columned table in the csv file? For example in the ODBC database we have: TABLE 1 +–…
Amazon Redshift – lateral column alias reference
Based on Amazon Redshift announces support for lateral column alias reference: The support for lateral column alias reference enables you to write queries without repeating the same expressions in the SELECT list. For example, you can define the alias ‘probability’ and use it within the same selec…
Select the last record in MySQL in WHERE condition
I have a table that store ticket and statue relation I want to select rows that last status_Id equal 2 ,Rows are marked in table. I think I have to use GROUP BY on column ticket_Id but it return with first status_Id. Answer This problem is a good candidate for ROW_NUMBER: The above logic finds all latest rows…
Execute SQL-LIKE with List attribute
I have this method on my Dao class: the sql works fine when the attribute is @Column or a @OneToOne`, but when it’s something like that: where the class Titulo has this attributes: causes this error: How I can change the method to make work for both types of attributes? Answer I manage to solve this iss…
How to get the value of selected option in html using find() in Node.js Express and mongodb
I’m trying to get a specific document in mongodb and I’m using find(). the problem is that it doesn’t give me the result that I’m looking for in the html file (for example: i choose MALE and XL which …
How to select from a column with a list of ids in postgresql
I’ve got mytable1 with row_number integer and list_of_ids int[] column mytable2 with id integer and company text columns Example entry for mytable1 Example entry for mytable2 I need to feed back values from mytable2 into mytable1. This way the expected output would be Answer You need to unnest the lists…
getting output of union all without using union all
I have been given a situation to find the common record between 2 tables without using union. I could do it. But I am not able to do ‘union all’. I mean I have to find out output of 2 table including …
Group rows by dense_rank() and loop through each sub-group and compare another column in next row of that sub group?
I have tried the following in LINQPad: I want to write a query which will return only the IDs 1 and 2 (not 3 and 4) because: ID 1 – has more than 1 rows and startdate of its rownum 2 is 1 day ahead of enddate of its rownum 1 ID 2 – has more than 1 rows and
Recursive CTE have performace issue, need suggestion to optimize query
I wanted to get TOP 5 records from log table where “Approve date” is changed like NULL to value and vice versa. Date value is doesn’t matter, but order matters. In this case I want first record and 5th record (Someone approved the data that’s why a value),then 7th record value is null someon…