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 »

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 »

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 »