Skip to main content

Concept Of ROW_NUMBER,RANK and DENSE_RANK

This post is Regarding Row_Number,RANK and Denserank.
Row_Number:= Using this function one can retuen unique id to each row.
Syntex:
SELECT
Col_Value,
ROW_NUMBER() OVER (ORDER BY Col_Value) AS 'RowID'
FROM
Table;
Result:
Col RowID
A 1
A 2
A 3
B 4
B 5
C 6
C 7
RANK:-This function will return a unique number to each distinct row, but it leaves a gap between the groups.
Syntex:-
SELECT
Col_Value,
Rank() OVER (ORDER BY Col_Value) AS 'RowID'
FROM
Table;
Result:-
Col RowID
A 1
A 1
A 1
B 4
B 4
C 6
C 6
DENSE_RANK:-This function is similar to Rank with only difference, this will not leave gaps between groups.
it generates a unique id for each group and without repetition.
Syntex:-
SELECT
Col_Value,
DENSE_RANK() OVER (ORDER BY Col_Value) AS 'RowID'
FROM
Table;
Result:-
Col RowID
A 1
A 1
A 1
B 2
B 2
C 3
C 3

Comments

Post a Comment

Popular posts from this blog

View Active Temp table In PostgreSQL

Purpose : Work with Temp table, Some time developer have trouble handle temp table or forgot to drop temp table in function. Use : By Using below query developer can know active temp table. Query : SELECT     n.nspname as SchemaName     ,c.relname as RelationName     , CASE c.relkind     WHEN 'r' THEN 'table'     WHEN 'v' THEN 'view'     WHEN 'i' THEN 'index'     WHEN 'S' THEN 'sequence'     WHEN 's' THEN 'special'     END as RelationType     ,pg_catalog.pg_get_userbyid(c.relowner) as RelationOwner                   ,pg_size_pretty(pg_relation_size(n.nspname ||'.'|| c.relname)) as RelationSize FROM pg_catalog.pg_class c LEFT JOIN pg_catalog.pg_namespace n             ...
Post a Comment
Read more

IN,ANY And ALL In PostgreSQL

IN,ANY And ALL  IN ANY And ALL are Keyword-Operator, Which is Used In WHERE Clause of SQL Statements. IN , ANY And ALL Returns Multiple Raw As per conditions. USE OF IN in SQL Statments:  SELECT * FROM tablename WHERE columnname IN ('value1','value2','value3','value4') SELECT * FROM tablename WHERE columnname IN   ( SELECT columnname FROM tablename WHERE Conditions..) SELECT * FROM tablename WHERE (col1,col2) IN (('val1','val2'),('val11','val22'))   SELECT * FROM tablename WHERE (col1,col2) IN (SELECT col1,col2 FROM table WHERE Conditions..)   USE OF ANY and ALL in SQL Statments: Used with a WHERE or HAVING clause . The ANY operator returns true if any of the subquery values meet the condition. The ALL operator returns true if all of the subquery values meet the condition. ANY Syntax SELECT column_name(s) FROM table_name WHERE colu...
Post a Comment
Read more