I’m trying to update a website where there’s new ID’s for different damage groups. All the new ID’s start after ID 139. In SQL the statement to get the new groups are simply: However, I want to implement the same statement in my ASP code and in the method that fetches the current damag…
Querying a list of geometry objects from Oracle based on list of x and y coordinates
We are using an Oracle database table with SDO_GEOMETRY objects. I would like to return the rows where the column with the geometry object contains a certain point (x, y coordinate). I know I can query the table using a single point geometry like this: But let’s say I have a list of coordinates and I wo…
how to group the data by some interval datetimes?
there are many devices and while using it will upload data every some seconds or minutes. I want to get the sections of date-time that the device is in use can it be true that group the data by an interval? let us consider interval = 1 minutes then group the data which the minus of the two date-time is
MySQL table update after multi field check
Hi I have two table one like this one: table1 and one like this: table2 I would like to update all the fields on the table2 column “newID” based on this rules: if (table2.ID = table1.ID_actual or table2.ID=table1.ID_old) then table2.newID = table1.newID How can I resolve this problem ? Answer You …
I am unable to upload a .csv file in postgresql database because I don’t know how to put date structure in sql query
I downloaded a csv file for practising where date format is of two types as shown in picture. the picture is here I tried to change the format to yyyy-mm-dd in excel but it is not happening. and also, I can’t upload the file in database in my postgresql. I used the data type “date” but it sa…
First row of all groups in Flask SQLAlchemy
I want to group the database by gauge_id and then get the row having maximum time_col. This is my current code: But I am only able to get both gauge_id and time_col. When I add another column (reading), like this: it gives this error. How do I return the top row of each groups? Thanks in advance. Answer One w…
MySQL Trigger Nested Group_Concat Duplicates
I have a trigger to conditionally GROUP_CONCAT a few columns. I can remove duplicates from within a single column, but I’m struggling to remove duplicates between two or more columns. Current trigger (two column example): Fiddle: https://dbfiddle.uk/?rdbms=mysql_8.0&fiddle=46a6bb915fa7e2e9106b073677eee6cd…
SQL statement to list final course grade for students taking two specific courses
This is my statement for the final course grade for all those students taking MATH, how can I adjust this so it displays the final course grade for MATH, but only for students who are taking both MATH and ART ? Answer You can do it with conditional aggregation: First, you should use a proper join with an ON c…
Output array type column
I have a table like so – How do I output this result – Answer For an array, you would use array_agg(): If you want the value as a comma-sseparated string:
mySQL update average amount of last 5 dates when there is gap in dates
I have mySQL table like below, Is there any way I could update the value in table2 to where ? is placed. need the last 5 Days Average Amount when the sales actually registered, from last 5 dates from table-1 for(9th, 7th, 3rd, 30th, 28th) Answer here is one way: to update :