SQL Analytical Functions/Utility queries


Find the Nth largest row


Partition by

Split the resultset into groups

Connect by

Represent hierarchical data

Fetch next n rows

Paginating the records in SQL

Lag & Lead

Compare current row with previous/next row(s)

Rank the data


Comments

Post a Comment

Popular posts from this blog

Distributed database design using CAP theorem

Image
CAP is a theorem/standard which can be applied while designing distributed systems like distributed databases. A distributed database is a set of databases(nodes) which are spread across the network.  These set of databases(nodes) in the distributed database can be either designed as either a  master/slave  or a  sharded model . Consistency -  Database should return the latest write on the DB. Availability -  Database should return data in a decent amount of time .  But, the data might not be latest. Partition tolerant - I n spite of n/w failure, the database should always be available. A distributed database can be designed either as a CP, AP, CA but not with all the 3 capabilities.  AP - Available and Partition tolerant  -  Sharded model (Data is distributed, No Master)  -      Availability  - Because, the data is distributed, any of the nodes can return the data. So, always availabl...
Continue Reading »

LDAP - Basics

Image
What is LDAP? LDAP is a protocol used by clients for accessing the directory server. What is a directory server? The directory server is a storage server which is widely used to store user's data of any organization. It uses LDAP protocol to listen to client requests for performing any read/write operations on it.  Since the directory servers support LDAP protocol for interacting with clients, they are also called as LDAP servers. Who are the clients to directory server? Directory clients can be anything which is capable of interacting with the directory server. Examples of directory clients can be a java application, Apacheds studio (Apache DS studio is similar to SQL developer or TOAD for Oracle), etc.  How data in LDAP servers gets stored? Data in LDAP server gets stored in a directory.  When we add entries to the LDAP, they get stored under the base directory. Some of the entries may contain subentries also. Eventually, This result in data stor...
Continue Reading »

Easy approach to work with files in Java - Java NIO(New input output)

  NIO - New IO package. Introduced in 1.4. It is buffer based and non-blocking. Hence, faster than IO package. It supports symbolic links, posix file attributes(works only on OS's which are POSIX compliant), ACL (Access control list). Paths and Files are the main classes Creating a potential file reference: Path path = Paths.get("c:/demo/"); //possible file reference Creating a directory, file, write to the file, read from the file: //create all the non existing directories in the path. Ignores creating a directory, if that directory already exists Path directories = Files.createDirectories("c:/demo/example1/properties/"); Path newFilePath = Files.createFile("c:/demo/README.txt"); //Creating a file //Writting bytes to the file Files.write(newFilePath, "This is a dummy text".getBytes(StandardCharsets.UTF_8)); //Reading bytes from file String readFile = new String(Files.readAllBytes(newFilePath), "UTF-8"); Files.readAllBytes reads all...
Continue Reading »