PostgreSQL: How to Delete all duplicate rows Except one
Database Research & Development Blog
by Anvesh Patel
3y ago
I have already written a similar article to delete duplicate records in SQL Server and MySQL. Here, You can also access that articles. Delete all duplicate rows in MySQL Delete all duplicates rows except one in SQL Server Recently, I got one request for one script to delete duplicate records in PostgreSQL. Most of the Database Developers have such a requirement to delete duplicate records from the Database. Like SQL Server, ROW_NUMBER() PARTITION BY is also available in PostgreSQL. I have prepared this script, using simple inner query with the use of ROW_NUMBER() PARTITION BY clause. C ..read more
Visit website
SQL Puzzle: SQL Advance Query – Find the Order basis on thier Status and Step
Database Research & Development Blog
by Anvesh Patel
3y ago
Check the below input data and require output data to find order which step is 0 with status D and for the same order other status are P. Input Data: OrderID Step Status ------- ----------- ------ ABC 0 D ABC 1 P ABC 2 P ABC 3 P XYZ 0 D XYZ 1 D EFQ 0 D Expected Output: OrderID ------- ABC Create a table with sample data: CREATE TABLE tbl_Orders ( OrderID CHAR(5) ,Step INTEGER ,Status CHAR(1) ) GO INSERT INTO tbl_Orders VALUES ('ABC', 0, 'D'), ('ABC', 1, 'P ..read more
Visit website
SQL Server: How to compare your objects between two Databases
Database Research & Development Blog
by Anvesh Patel
3y ago
When we are working with different versions of database, database professionals are comparing the objects between databases of SQL Server. They must make sure about that all databases are in sync. Using this T-SQL Script, you can compare two databases and find a list of unmatched objects like: Constraints, Tables, Views, Stored Procedure, Triggers. Sample demonstration: First, Create two sample databases: CREATE DATABASE ABC GO CREATE DATABASE XYZ GO Create few sample tables in both the databases: CREATE TABLE ABC.dbo.tbl_Test ( ID INT ,MyDate DATE ) GO CREATE TABLE ABC.dbo ..read more
Visit website
SQL Puzzle: SQL Advance Query – Generate the group of Sequences
Database Research & Development Blog
by Anvesh Patel
3y ago
Check the below input data and expected output to generate the group of sequences. Input Data: Name SeqNo ---- ----------- A 1 A 2 A 3 B 1 B 2 C 1 C 2 C 3 D 1 D 2 D 3 D 4 D 5 Expected Output: Name SequenceNo_Groups ---- -------------------- A 11,12,13 A 11,12,13 A 11,12,13 B 21,22 B 21,22 C 31,32,33 C 31,32,33 C 31,32,33 D 41,42,43,44,45 D 41,42,43,44,45 D 41,42,43,44,45 D 41,42,43,44,45 D 41,42,43,44,45 Create a table with sample data: CREATE TABLE TestSequences ( Name VARCHAR(3 ..read more
Visit website
PostgreSQL: Create Index on Full Text Search tsvector Data
Database Research & Development Blog
by Anvesh Patel
3y ago
In this post, I am sharing an example of applying Full Text Search on PostgreSQL Table with Index. Here, I am using tsvector for full text search which is document type and uses match operator like @@. PostgreSQL: Example of Trigram Index for Full Text Search using pg_trgm Extension Below is an example: Create a sample table with data: CREATE TABLE tbl_Textdata (id int, txt character varying); INSERT INTO tbl_Textdata VALUES (1,'I am DBA'),(2,'database research & development'),(3,'think for data only') ,(4,'data is important'),(5,'data is a future'),(6,'I am data owner'); Do Full ..read more
Visit website
SQL Puzzle: SQL Advance Query – Generate the range of data basis on common combination
Database Research & Development Blog
by Anvesh Patel
3y ago
Check the below input data and expected output to generate the report on range basis on the common combination of SendFlag and ReceiveFlag. Input Data: ID CreationDate SendFlag ReceiveFlag ----------- ------------ -------- ----------- 1 2016-08-08 0 0 2 2016-10-01 0 0 3 2016-11-26 1 0 4 2016-12-26 1 0 5 2017-01-08 1 0 6 2017-03-10 1 1 7 2017-06-20 1 1 8 2017-08-15 0 1 Expected Output: StartID EndID SendFlag ReceiveF ..read more
Visit website
Database Theory: What is CAP theorem – Consistency, Availability, Partition
Database Research & Development Blog
by Anvesh Patel
3y ago
We all are aware about the ACID Properties of Database System (Atomicity, Consistency, Isolation, Durability). If you don’t know about the ACID, please visit below article. ACID Properties in Database System (Atomicity, Consistency, Isolation, Durability) In last one decade, lots of new Database Products available in the Market. The types are: RDBMS Products, NoSQL Products, BIGDATA, MPP Products. My Question is, how do you choose best database product for your project? Traditional Answer, The first preference of Database Designer would be ACID Properties oriented Databases. But most of the ..read more
Visit website
PostgreSQL: Example of SERIALIZABLE Isolation Level
Database Research & Development Blog
by Anvesh Patel
3y ago
THE SERIALIZABLE Isolation level is one kind of extended version of the REPEATABLE READ Isolation level. Now with the SERIALIZABLE Isolation level, you cannot modify the data while another transaction is reading the same data. For SELECT-only transactions, use the SERIALIZABLE isolation level when you do not want to see the other transaction commits during your transaction. For UPDATE and DELETE transactions, SERIALIZABLE isolation prevents concurrent modification of the same data row; it should therefore be used with caution. Create a table with few sample records: CREATE TABLE tbl_Employee ..read more
Visit website
SQL Server: Script to check Network or Connect permission of User
Database Research & Development Blog
by Anvesh Patel
3y ago
In this post, I am sharing a script for checking the Network and Connect related permission of SQL Server User. When you installed the SQL Server, for network access you should enable the TCP/IP pipeline and other connect permission so that others can access the server. Using below script, you can easily identify those permissions which are important for connecting SQL Server. I took the reference from this official page. SELECT EP.name, SP.STATE, CONVERT(nvarchar(38), suser_name(SP.grantor_principal_id)) AS GRANTOR, SP.TYPE AS PERMISSION, CONVERT(nvarchar(46),suser_name ..read more
Visit website
SQL Puzzle: SQL Advance Query – To generate the account balance column for Bank Accounts
Database Research & Development Blog
by Anvesh Patel
3y ago
Check the below input data and expected output to prepare the report on bank account transaction like generate the data of AccountBalance column where for CR plus the transaction value and for DR minus the transaction value. Input Data: TransactionDate AccName Type Amount --------------- ---------- ---- ------------- 2017-01-01 Anvesh CR 60000.00 2017-02-01 Anvesh DB 8000.00 2017-03-01 Anvesh CR 8000.00 2017-04-01 Anvesh DB 5000.00 2017-01-01 Nupur CR 10000.00 2017-02-02 Nupur CR 8000.00 2017-03-03 Nupur D ..read more
Visit website

Follow Database Research & Development Blog on Feedspot

Continue with Google
OR