I want to apply pagination on a table with huge data. All I want to know a better option than using OFFSET in SQL Server. Here is my simple query: Answer You can use Keyset Pagination for this. It’s far more efficient than using Rowset Pagination (paging by row number). In Rowset Pagination, all previou…
Tag: sql
ADO.NET: Send procedure parameter with null value
I need to send a parameter a null value to the procedure, but I get an error: System.Data.SqlClient.SqlException: ‘Procedure or function ‘sel_mizanyeni’ expects parameter ‘@subeno’, which was not supplied.’ When I run the same procedure with the same parameters in SQL Serve…
How to call a complex SQL query in python
I have the following SQL code: This SQL code has been tested and runs perfectly on SQL Server Management Studio. Now I have the following code for python which is to communicate with an MS SQL Server instance: When I run it, I get the following error where it points to the first semicolon in my code. Could yo…
using a sql request in spark sql error in execution
I try to execute this query in pyspark i get all the time error. I have looked everywhere but I don’t know or it doesn’t work if someone can help me. the goal of this request is to update a new column that I will later create called temp_ok : this my code: My table contains this columns: _temp_ok_…
MySQL error: HAVING is not valid at this position, expecting EOF, ‘;’
Im having that problem in this specific line: Probably is a stupid error, but im not seeing a syntax problem here. Answer SELECT city, SUM(credit_limit) AS total_limit FROM table GROUP BY city ORDER BY total_limit WHERE SUM(credit_limit) < 110000;
Finding duplicate values in multiple colums in a SQL table and count
I have the following table structure: I use the following command to display the duplicates and the counts: This command gives me a count of 2. I would like to know how the second line could also be counted as a dublicate. Answer You need the scalar functions MIN() and MAX() to get the 3 integer values of eac…
Group by range of dates from date_start to date_end columns
I have a table with following table structure: I want to count how many events (each row is an event) was in every place by each month. If event dates refer to several months, it should be counted for all affected months. place_id could be repeated, so I did the following query: So I get following grouped tab…
UNPIVOT in Snowflake and adding additional columns
I am trying to unpivot a table TABLE_A and add additional columns to the query TABLE_A: YEAR ID OCT NOV DEC JAN FEB MAR APR MAY JUN JUL AUG SEP 2022 1 10 5 5 10 5 10 15 20 5 0 0 10 Expected output: ID MONTHLY_NO MONTH START_DATE YEAR 1 10 10 10-01-2021 2021 1 5 11 11-01-2021
LTRIM RTRIM not working for Chinese string SQL
I have a column named Text which receives from the end user the following string: ‘复合模头滤网 φ245 120目*300目 24×120目 ’ Which includes a weird space, kind of larger than the regular space, at the end. However it has the same ASCII code as the normal space 32. I used this SQL code to trim my string but …
How to get data types of columns when a CREATE TABLE FROM SELECT is forbidden?
I’ve finished a big query in SSMS with a lot of columns. The result will be inserted in another server. Usually, I create a temporary table to quickly get the data types of each columns (CREATE TABLE from a SELECT). But now, I’m working on a server where it is forbidden to create a table (even a t…