I have this dataset: I want the top 2 counts per the_city, so the expected result should be: I have tried this but it’s wrong Answer It’s greatest-n-per-group problem. You can use row_number()over() window function to to serialized city wise product list in descending order of count(*). Then selec…
Tag: sql
Laravel/MariaDB: errno 150 “Foreign key constraint is incorrectly formed”
I’m on Laravel 5.4, PHP 5.6, Ubuntu 18.04, MariaDB 10.4.8. When I run php artisan migrate, I get: I’m trying to use https://github.com/klisl/laravel-comments. Before trying to perform a migration with this package I had created DB at phpMyAdmin, had configured .env by adding DB name and stuff, had…
Query to select all tables with specific last column Ibm Db2 z/os
Goal: I need to create a query to select all tables with specific last column Ibm Db2 z/os So I know I need to change my where clause for this but pretty much I want to select all the tables in my schema that have the last column as BATCH_ID. I’ve tried a length based where clause but can’t figure
Using SQL, when there are multiple columns with dates. how can I determine and return a row where the date is between the other rows?
I am building a table for a data warehouse that needs to have a row for each change that occurs. The issue is that there are sometimes changes that occur in the subgroups and I can’t figure out how to show those changes. For example, I have the following table: RowNumber Code CorrectedProductYear Produc…
ERROR cursor does not exist after first loop in PL/pgSQL
I need to load a large number of csv files in to a PostgreSQL database. I have a table source_files which contains the file paths and a flag which indicates whether a file has already been loaded for all the csv files I need to load. I have written the following code which loads the first file correctly but t…
In SQL how to find data rows separated within few seconds of each other
I have a table in which sensor events are recorded. And a random log is selected for testing every hour at a random time. I want to find N events which are within T seconds of each hourly picking. Data looks like this Event 1 and 3 both happened within 4 seconds of each other. I want to find all
How to convert decimal values to time values in oracle?
I have a table that is formatted like this: I want the output to be like this: Basically transform the values in the dec_time field into time values like the example above in new_dec_time. This is my SQL so far: This simply changes replaces the decimal point with the semicolon but doesnt add the 0s where need…
SQL – Adding columns
I am new to SQL and was wondering if someone can help me with this small request. I am trying to have my output like down below: But I am currently getting this output: Here is my SQL code: How can I get the columns to reflect MF, CH_A, CH_B, and CH_C with its respective value? Answer @Med2020 Your query
T-SQL :: Error converting data type varchar to numeric when changing 1 to 0.5
I’m writing a complex T-SQL query with CASE that will help me calculate how much time it will take me to migrate databases based on the size of each database: All good, it works when I set 1 hour of work for every database which has less then 100.00 MB. Also all other values are summed and in the Hours
How to find a destinct values in a table that are larger than all values that reference it?
I need to find the names of bosses who were hired after all the hired date of all their subordinates. Here is what I have got so far: SELECT DISTINCT TRIM(boss.first_name || ‘ ‘ || boss.last_name)…