Here is my MySQL table. I updated the question by adding an ‘id’ column to it (as instructed in the comments by others). I want to add a new column called row_num to assign an incrementing number ONLY for duplicates, as shown below. Order of the results does not matter. I followed this answer and …
Tag: mysql
Proper use of MySQL Memory Tables for performance
I have a table that has 60 columns (and thousands of rows) and it references to 15 catalog tables and 3 of those reference to another 3 catalog tables (the whole database is less than 1 GB). These …
Mysql – How to create view representing dynamic pivot
I have two database tables customers which contains data about customers with the scheme like that: mysql> SELECT * FROM customers; customer_id created_at partner_id 1 “…
Aggregating data with SQL if other values in rows meet certain criteria
I’m pretty new to SQL and am trying to figure out if there’s a way to aggregate the date into new column. For instance, below is a sample set of date. I want to take all the type ‘2’ and create new …
Selecting different parameters for a group by statement in mysql?
I have a dataset like this: Here in this dataset, there is No NULL value for all these 3 columns, also user_name and recording_msid pair can repeat but each repeated pair will have a distinct …
mySQL – Check Table for duplicates efficiently
I have a table “people” where (for simplicity) everyone has an ID, this ID is not unique so two entries can have the same ID. I now want to find all duplicates which I would do like this: SELECT p1….
Search data based on two columns SQL
I have a database table like this: I need search from Id the Id’s that not have Tipo = Inventario and Create a new line with that data, I mean for example, RTOR-00261 and RTOR-0255 dont have INVENTARIO, I need to create new with the fields that there is in SUSTRATO. All data are in the same table Final …
Comparing count(*) with select for one result
I have a table tb_xyz having field1, field2 as indexed. Now I have two queries: and Assuming both queries are written to just check if this condition is met at least once. Which of them is more optimal. I tried checking for their execution times, but results were mixed, for small data. Answer The first query …
Query based on some data multiple compare
Table: How do i bring the period when customers have max cc_limit? If the cc_limits same for customer for more than one month, take the period with highest year-month data Answer Use row_number() window function,Below should work. EDIT : To clarify why order by should be in the inline view. Scenario : When th…
SQL insert query with some condition
I am using MySQL. There are three tables presented: Patient, Occupies, Room and Privte_Patient. I need to identify the first available room and allocate the room to a newly admitted patient with PIN ‘…