Jenny's Website

Today let’s practice how to check the ranks of the data with the function, RANKING.

And also this post includes the function GROUP BY, PARTITION BY, ORDER BY, COUNT().

These are the codes I often used when managing DB data at the previous company. Hope it helps you too.

Here is the SQL codes with some examples below:

-- CHECK ALL COLUMNS 
-- select * means all variables
-- That is, the following code can print all variables in table
SELECT * FROM DB.ADMIN.TABLENAME 


-- CHECK EACH VARIABLES 
-- Check how many items of variable VAR1 are
-- Meaning of 1st code: It leaves count variables, and the variable name is COUNT
SELECT DISTINCT VAR1, COUNT (*) AS COUNT 
  FROM DB.ADMIN.TABLENAME 
  GROUP BY VAR1 
; 

  
-- CHECK PK (Primary Key) 
-- Ranking within the group
-- Determine groups with VAR1 and sort the order with VAR2
-- Leave the row number as a variable, the name of the variable is RANKING
SELECT *, 
Row_number() over(PARTITION BY VAR1 ORDER BY VAR2 desc) RANKING 
FROM DB.ADMIN.TABLENAME 


-- CHECK PK-UNIQUE VALUE
-- COUNT items by grouping VAR1, VAR2, and VAR3 with a separator
-- pk can be checked for uniqueness - all must be 1
SELECT VAR1, VAR2, VAR3, COUNT (*) AS COUNT
  FROM DB.ADMIN.TABLENAME 
  GROUP BY VAR1, VAR2, VAR3


-- CODE AND NAME MATCH
-- Check if VAR1_CD and VAR1_NM match 1:1
SELECT DISTINCT VAR1_CD, VAR1_NM  
    FROM DB.ADMIN.TABLENAME 


-- CHECK SPECIFIC VALUE
-- Use where statement to print rows with specific values
-- Useful when extracting specific values
SELECT VAR1, VAR2, VAR3
    FROM DB.ADMIN.TABLENAME
    WHERE VAR1 = 'Endometrioid-'


-- COUNT COLUMN OBS
-- Count the number of observations for each variable
SELECT count(VAR1)
    FROM DB.ADMIN.TABLENAME

SELECT count(VAR1),count(VAR2),count(VAR3),count(VAR4)
    FROM DB.ADMIN.TABLENAME