I am fighting with the distinct keyword in sql. I just want to display all row numbers of unique (distinct) values in a column & so I tried: however the below code giving me the distinct values: but when tried it with Row_Number. then it is not working. Answer Use this: and put the “output” of…
Tag: sql
Fastest way to determine if record exists
As the title suggests… I’m trying to figure out the fastest way with the least overhead to determine if a record exists in a table or not. Sample query: Say the ? is swapped with ‘TB100’… both the first and second queries will return the exact same result (say… 1 for this c…
How to create a large pandas dataframe from an sql query without running out of memory?
I have trouble querying a table of > 5 million records from MS SQL Server database. I want to select all of the records, but my code seems to fail when selecting to much data into memory. This works: …but this does not work: It returns this error: I have read here that a similar problem exists when c…
WHERE CASE WHEN statement with Exists
I am creating a SQL query having WHERE CASE WHEN statement. I am doing something wrong and getting error. My SQL statement is like I am getting error as Msg 512, Level 16, State 1, Line 11 Subquery returned more than 1 value. This is not permitted when the subquery follows =, !=, <, <= , >, >= or …
Generate CREATE statements for all MySql Tables
I want All CREATE statements of MySql Tables in 1 query result. For example, INFORMATION_SCHEMA contains all table names,comments etc. but where are the CREATE statements are stored in MySql ? can it be retrieved in one query for all tables ? Currently i am retrieving TABLE ddl as below for 1 table. I have 10…
How can a blank MS Access database be created using VBA?
I’m a total noob trying to create a blank MS Access database using VBA in Excel. I want to name the new database “tblImport”. This is the code I´m using: I get the following error message: “Run Time Error 3001: Application Defined or Object Defined Error” What can I do? Answer Th…
In Derby, is there a way to create a database table that has a column that defaults to the UTC timestamp?
I use Derby as a solution for whitebox testing my code. In what will become my production database, I have a column that defaults to the system timestamp in UTC. However; I cannot figure out how to do …
Check If Date Falls In Period With Year Unspecified
I’ve got a table that defines several non-overlapping date periods using only month and day. It’s basically a “stocking” table for inventory items that allows you to stock different amounts across the year for seasonal businesses. Here’s an example of how a single item’s st…
How to execute SQL statements saved in a table with T-SQL
Is it possible to execute a SQL statement Stored in a Table, with T-SQL? The statements that are stored in the table are ad-hoc statements which could be SELECT TOP 100 * FROM ATable to more complex statements: I want to execute the @Query Variable that I declared from T-SQL. Is this possible? (I am running a…
Foreign Key constraints missing after phpmyadmin export
I create a table in mysql using the following script: When I export the created table from phpMyAdmin, I obtain the following script So the question are: where is my foreign key constraints? does KEY refer to FK? Seems that the two tables utente and attivita are no longer referenced in the new generated scrip…