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 »

SQL Analytical Functions - Partition by (to split resultset into groups)

Partition by:  Splits the resultset into groups.  Employees -Table EMPLOYEE_ID FIRST_NAME SALARY ----------- -------------------- ---------- 100 Steven 24000 101 Neena 17000 102 Lex 17000 103 Alexander 9000 104 Bruce 6000 105 David 4800 106 Valli 4800 107 Diana 4200 select e.employee_id, e.department_id, salary, row_number() over (partition by department_id order by salary desc) as rownumber from employees e; The above query splits the result into small subgroups for every department.  Syntax :  Function(arg1,..., argn) OVER ( [PARTITION BY <..>] [ORDER BY <....>]  [<window_clause>] ) partition by  - create  subgroups for e...
Continue Reading »