Skip to content

Tag: sql

SQL Select from multiple tables and count third

I want to count number of rows in table that is referenced by two fields of other tables. I’ve the following structure Result that I want to achieve The statement that I’ve tried, but getting error at right join What I’m missing? Answer You are quite close. Main problems with your query: you…

select starting digitals followed by first word in mysql

I have lots of addresses in my database. example: 1199 State Route 218 I want to select the starting digitals followed by first word and ignore the rest of the address. so i want to retrieve address just by calling this: 1199 State thanks for you help Answer If you are running MySQL 8.0, you can use regexp_su…

Generate series of random size data in MySQL

I need to populate table with random size of data for each dependency record. First table is table of products (named products): And the second table is table that contains displays of that product (named product_displays). Each display is another row: Like you can see date will always be the same value, but …

SQL – get specific text from string

I have column with this strings: I need to get from the string ONLY the contact_type__* part (appears only once in each row) Any suggestions? Answer You could use regexp function regexp_extract(), like: This will match on string ‘contact_type__ followed by a sequence of letters, numbers, or underscore (…

SQL Query/ Assigning Rank

I am interested in SQL query not the PLSQL code. We need to assign the rank based on date and id value Input table should look like below output table should look like below Answer This is a type of gaps-and-islands problem. In this case, the simplest solution is probably the difference of row numbers: Why th…

process/transaction data partitioning

i have problem with partitioning the process data i have from our workflow system. data are stored in database step after step (one step is one row) for couple of processes (id) and a date of the event. in simplified data is looks as follows: what i need to have is such short table the “x” step is…