I need to update single column for all the rows in a table. The update should happen in such way that data needs be filtered out by removing all text where braces are appended for the string and swap the strings separated by ‘,’. Example: As per the above example, I need the final data to be somet…
Tag: sql
How to query the number of records when there is the DISTINCT keyword in the SQL multi-table join query string?
— table1 has 1 record — table2 has 4 record — t_test01.value = t_test02.value, 3 record — DISTINCT t_test01.value = t_test02.value, 1 record How to query the number of records when there is the DISTINCT keyword in the SQL multi-table join query string? Answer A quick-and-dirty approach…
Effectively select latest row for each group in a very large table?
I have (for example’s sake) a table Users (user_id, status, timestamp, …). I also have another table SpecialUsers (user_id, …). I need to show each special user’s latest status. The problem is that the Users table is VERY, VERY LARGE (more than 50 Billion rows). Most of the solutions i…
add sql WHERE statement to select new ID:s in database (asp / sql)
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…