Chapter 9 – Distinct Vs Group By AND TOP
by Ed Bernier, Tom Coffing
Actian Matrix (Formely ParAccel) - Architecture and SQL
- Cover
- The Tera-Tom Video Series
- The Tera-Tom Genius Series
- Tera-Tom- Author of over 50 Books
- The Best Query Tool Works on all Systems
- Trademarks and Copyrights
- About Tom Coffing
- About Ed Bernier
- Contents
- Chapter 1 – What is Columnar?
- What is Parallel Processing?
- The Basics of a Single Computer
- Data in Memory is fast as Lightning
- Parallel Processing Of Data
- A Table has Columns and Rows
- Rows are Placed Inside a Data Block
- Moving Data Blocks is Like Checking In Luggage
- Facts That Are Disturbing
- Why Columnar?
- Row Based Blocks vs. Columnar Based Blocks
- As Row-Based Tables Get Bigger, the Blocks Split
- Data Blocks Are Processed One at a Time Per Unit
- Columnar Tables Store Each Column in Separate Blocks
- Visualize the Data – Rows vs. Columns
- The Architecture of Actian Matrix
- Matrix has Linear Scalability
- Distribution Styles
- Distribution Key Where the Data is Unique
- Another Way to Create a Table
- Distribution Key Where the Data is Non-Unique
- Even Distribution Key
- Matching Distribution Keys for Co-Location of Joins
- Big Table / Small Table Joins
- Fact and Dimension Table Distribution Key Designs
- Improving Performance By Defining a Sort Key
- Sort Keys Help Group By, Order By and Window Functions
- Each Block Comes With Metadata
- How Data Might Look On A Slice
- Question – How Many Blocks Move Into Memory?
- Answer – How Many Blocks Move Into Memory?
- Quiz – Master that Query With the Metadata
- Answer to Quiz – Master that Query With the Metadata
- The ANALYZE Command Collects Statistics
- Matrix Automatically ANALYZES Some Create Statements
- What is a Vacuum?
- When is a Good Time to Vacuum?
- The VACUUM Command Grooms a Table
- The Matrix database catalog also needs periodic vacuuming and indexing
- Database Limits
- Creating a Database
- Creating a User
- Dropping a User
- Inserting Into a Table
- Renaming a Table or a Column
- Adding and Dropping a Column to a Table
- Chapter 2 – Best Practices for Table Design
- Converting Table Structures to Actian Matrix
- Converting Table Structures to Actian Matrix Finale
- Best Practices for Designing Tables
- Choose the Best Sort Key
- Each Block Comes With Metadata
- Creating a Sort Key
- Sort Keys Help Group By, Order By and Window Functions
- Choose a Great Distribution Key
- Distribution Key Where the Data is Unique
- Matching Distribution Keys for Co-Location of Joins
- Big Table / Small Table Joins
- Define Primary Key and Foreign Key Constraints
- Primary Key and Foreign Key Examples
- Use the Smallest Column Size When Creating Tables
- Use Date/Time Data Types for Date Columns
- Specify Redundant Predicates on the Sort Column
- Setting the statement_timeout to Abort Long Queries
- Chapter 3 – Systems Tables
- Actian Matrix System Tables
- Trouble Shooting Catalog Table pg_table_def
- Seeing the System Tables in your Nexus Tree
- Catalog Table pg_table_def
- Checking Tables for Skew (Poor Distribution)
- Checking All Statements That Used the Analyze Command
- Checking Tables for Skew (Poor Distribution)
- Checking for Details about the Last Copy Operation
- Checking When a Table Has Last Been Analyzed
- Checking For Column Information on a Table
- System tables for troubleshooting data loads
- Determining Whether a Query is Writing to Disk
- Chapter 4 – Compression
- Chapter 5 - Temporary Tables
- Create Table Syntax
- Basic Temporary Table Examples
- More Advanced Temporary Table Examples
- Advanced Temporary Table Examples
- Table Limits and CTAS
- Performing a Deep Copy
- Deep Copy Using the Original DDL
- Deep Copy Using A CTAS
- Deep Copy Using A Create Table LIKE
- Deep Copy by Creating a Temp Table and Truncating Original
- CREATING A Derived Table
- The Three Components of a Derived Table
- Naming the Derived Table
- Aliasing the Column Names in the Derived Table
- Visualize This Derived Table
- Most Derived Tables Are Used To Join To Other Tables
- Multiple Ways to Alias the Columns in a Derived Table
- Our Join Example with a Different Column Aliasing Style
- Column Aliasing Can Default for Normal Columns
- CREATING a Derived Table using the WITH Command
- Our Join Example With the WITH Syntax
- WITH Statement That Uses a SELECT *
- A WITH Clause That Produces Two Tables
- The Same Derived Query shown Three Different Ways
- Quiz - Answer the Questions
- Answer to Quiz - Answer the Questions
- Clever Tricks on Aliasing Columns in a Derived Table
- A Derived Table lives only for the lifetime of a single query
- An Example of Two Derived Tables in a Single Query
- Connecting To Matrix Via Nexus
- Connecting To Matrix Via Nexus
- Connecting To Matrix Via Nexus
- Connecting To Matrix Via Nexus
- Chapter 6 – Explain
- Three Ways to Run an EXPLAIN
- EXPLAIN – Steps, Segments and Streams
- EXPLAIN Terms for Scans and Joins
- EXPLAIN Terms for Aggregation and Sorts
- EXPLAIN Terms for Set Operators and Miscellaneous Terms
- EXPLAIN Terms for Set Operators and Miscellaneous Terms
- EXPLAIN Example and the Cost
- EXPLAIN Example and the Rows
- EXPLAIN Example and the Width
- Simple EXPLAIN Example and the Costs
- EXPLAIN Join Example Using DS_BCAST_INNER
- EXPLAIN Join Example Using DS_DIST_NONE
- EXPLAIN Showing DS_DIST_NONE Visually
- EXPLAIN With a Warning
- EXPLAIN for Ordered Analytics Such as CSUM
- EXPLAIN for Scalar Aggregate Functions
- EXPLAIN for HashAggregate Functions
- EXPLAIN Using Limit, Merge and Sort
- EXPLAIN Using a WHERE Clause Filter
- EXPLAIN Using the Keyword Distinct
- EXPLAIN for Subqueries
- Chapter 7 - Basic SQL Functions
- Finding the Current Schema on the Leader Node
- Getting Things Setup in Your Search Path
- Five Details You Need To Know About the Search_Path
- Introduction
- SELECT * (All Columns) in a Table
- SELECT Specific Columns in a Table
- Commas in the Front or Back?
- Place your Commas in front for better Debugging Capabilities
- Sort the Data with the ORDER BY Keyword
- ORDER BY Defaults to Ascending
- Use the Name or the Number in your ORDER BY Statement
- Two Examples of ORDER BY using Different Techniques
- Changing the ORDER BY to Descending Order
- NULL Values sort First in Ascending Mode (Default)
- NULL Values sort Last in Descending Mode (DESC)
- Major Sort vs. Minor Sorts
- Multiple Sort Keys using Names vs. Numbers
- Sorts are Alphabetical, NOT Logical
- Using A CASE Statement to Sort Logically
- How to ALIAS a Column Name
- A Missing Comma can by Mistake become an Alias
- Comments using Double Dashes are Single Line Comments
- Comments for Multi-Lines
- Comments for Multi-Lines as Double Dashes Per Line
- A Great Technique for Comments to Look for SQL Errors
- Chapter 8 – The WHERE Clause
- Using Limit to bring back a Sample
- Using Limit with an Order By Statement
- The WHERE Clause limits Returning Rows
- Using a Column ALIAS throughout the SQL
- Double Quoted Aliases are for Reserved Words and Spaces
- Character Data needs Single Quotes in the WHERE Clause
- Character Data needs Single Quotes, but Numbers Don’t
- NULL means UNKNOWN DATA so Equal (=) won’t Work
- Use IS NULL or IS NOT NULL when dealing with NULLs
- NULL is UNKNOWN DATA so NOT Equal won’t Work
- Use IS NULL or IS NOT NULL when dealing with NULLs
- Using Greater Than or Equal To (>=)
- AND in the WHERE Clause
- Troubleshooting AND
- OR in the WHERE Clause
- Troubleshooting Or
- Troubleshooting Character Data
- Using Different Columns in an AND Statement
- Quiz – How many rows will return?
- Answer to Quiz – How many rows will return?
- What is the Order of Precedence?
- Using Parentheses to change the Order of Precedence
- Using an IN List in place of OR
- The IN List is an Excellent Technique
- IN List vs. OR brings the same Results
- Using a NOT IN List
- A Technique for Handling Nulls with a NOT IN List
- Another Technique for Handling Nulls with a NOT IN List
- BETWEEN is Inclusive
- NOT BETWEEN is Also Inclusive
- LIKE command Underscore is Wildcard for one Character
- LIKE Command Works Differently on Char Vs Varchar
- The Ilike Command Is NOT Case Sensitive
- Troubleshooting LIKE Command on Character Data
- Introducing the TRIM Command
- Quiz – What Data is Left Justified and what is Right?
- Numbers are Right Justified and Character Data is Left
- Answer – What Data is Left Justified and what is Right?
- An Example of Data with Left and Right Justification
- A Visual of CHARACTER Data vs. VARCHAR Data
- Use the TRIM command to remove spaces on CHAR Data
- Like and Your Escape Character of Choice
- Like and the Default Escape Character
- Similar To Operators
- Similar To Operators
- Similar To Example with Lower Case Letters
- Similar To Example with Lower and Upper Case Letters
- Similar To Example with Multiple Occurrences
- Multiple Occurrences Must Be Consecutive
- Chapter 9 – Distinct Vs Group By AND TOP
- Chapter 10 - Aggregation
- Quiz – You calculate the Answer Set in your own Mind
- Answer – You calculate the Answer Set in your own Mind
- The 3 Rules of Aggregation
- There are Five Aggregates
- Quiz – How many rows come back?
- Answer – How many rows come back?
- Troubleshooting Aggregates
- GROUP BY when Aggregates and Normal Columns Mix
- GROUP BY delivers one row per Group
- GROUP BY Dept_No or GROUP BY 1 the same thing
- Limiting Rows and Improving Performance with WHERE
- WHERE Clause in Aggregation limits unneeded Calculations
- Keyword HAVING tests Aggregates after they are Totaled
- Keyword HAVING is like an Extra WHERE Clause for Totals
- Chapter 11 – Join Functions
- A Two-Table Join Using Traditional Syntax
- A two-table join using Non-ANSI Syntax with Table Alias
- You Can Fully Qualify All Columns
- A two-table join using ANSI Syntax
- Both Queries have the same Results and Performance
- Quiz – Can You Finish the Join Syntax?
- Answer to Quiz – Can You Finish the Join Syntax?
- Quiz – Can You Find the Error?
- Answer to Quiz – Can You Find the Error?
- Super Quiz – Can You Find the Difficult Error?
- Answer to Super Quiz – Can You Find the Difficult Error?
- Quiz – Which rows from both tables won’t Return?
- Answer to Quiz – Which rows from both tables Won’t Return?
- LEFT OUTER JOIN
- LEFT OUTER JOIN Results
- Left Outer Joins Compatible with Oracle
- RIGHT OUTER JOIN
- RIGHT OUTER JOIN Example and Results
- Right Outer Joins Compatible with Oracle
- FULL OUTER JOIN
- FULL OUTER JOIN Results
- Which Tables are the Left and which are the Right?
- Answer - Which Tables are the Left and which are the Right?
- INNER JOIN with Additional AND Clause
- ANSI INNER JOIN with Additional AND Clause
- ANSI INNER JOIN with Additional WHERE Clause
- OUTER JOIN with Additional WHERE Clause
- OUTER JOIN with Additional AND Clause
- OUTER JOIN with Additional AND Clause Results
- Quiz – Why is this considered an INNER JOIN?
- The DREADED Product Join
- The DREADED Product Join Results
- The Horrifying Cartesian Product Join
- The ANSI Cartesian Join will ERROR
- Quiz – Do these Joins Return the Same Answer Set?
- Answer – Do these Joins Return the Same Answer Set?
- The CROSS JOIN
- The CROSS JOIN Answer Set
- The Self Join
- The Self Join with ANSI Syntax
- Quiz – Will both queries bring back the same Answer Set?
- Answer – Will both queries bring back the same Answer Set?
- Quiz – Will both queries bring back the same Answer Set?
- Answer – Will both queries bring back the same Answer Set?
- How would you Join these two tables?
- An Associative Table is a Bridge that Joins Two Tables
- Quiz – Can you Write the 3-Table Join?
- Answer to Quiz – Can you Write the 3-Table Join?
- Quiz – Can you Write the 3-Table Join to ANSI Syntax?
- Answer – Can you Write the 3-Table Join to ANSI Syntax?
- Quiz – Can you Place the ON Clauses at the End?
- Answer – Can you Place the ON Clauses at the End?
- The 5-Table Join – Logical Insurance Model
- Quiz - Write a Five Table Join Using ANSI Syntax
- Answer - Write a Five Table Join Using ANSI Syntax
- Quiz - Write a Five Table Join Using Non-ANSI Syntax
- Answer - Write a Five Table Join Using Non-ANSI Syntax
- Quiz –Re-Write this putting the ON clauses at the END
- Answer –Re-Write this putting the ON clauses at the END
- Chapter 12 – Date Functions
- Current_Date
- TIMEOFDAY()
- SYSDATE Returns a Timestamp with Microseconds
- GETDATE Returns a Timestamp without Microseconds
- Add or Subtract Days from a date
- The ADD_MONTHS Command Returns a Timestamp
- The ADD_MONTHS Command with Trunc Removes Time
- ADD_MONTHS Command to Add 1-Year or 5-Years
- Dateadd Function and Add_Months Function are Different
- The EXTRACT Command
- EXTRACT from DATES and TIME
- EXTRACT with DATE and TIME Literals
- EXTRACT of the Month on Aggregate Queries
- The Datediff command
- The Datediff Function on Column Data
- The Date_Part Function Using a Date
- The Date_Part Function Using a Time
- Date_Part Abbreviations
- The to_char command
- Conversion Functions
- Conversion Function Templates
- Conversion Function Templates Continued
- Formatting a Date
- A Summary of Math Operations on Dates
- Using a Math Operation to find your Age in Years
- Date Related Functions
- A Side Title example with Reserved Words as an Alias
- Implied Extract of Day, Month and Year
- DATE_PART Function
- DATE_PART Function using an ALIAS
- DATE_TRUNC Function
- DATE_TRUNC Function using TIME
- MONTHS_BETWEEN Function
- MONTHS_BETWEEN Function in Action
- ANSI TIME
- ANSI TIMESTAMP
- Matrix TIMESTAMP Function
- Matrix TO_TIMESTAMP Function
- Matrix NOW() Function
- Matrix TIMEOFDAY Function
- Matrix AGE Function
- Time Zones
- Setting Time Zones
- Using Time Zones
- Intervals for Date, Time and Timestamp
- Using Intervals
- Troubleshooting the Basics of a Simple Interval
- Interval Arithmetic Results
- A Date Interval Example
- A Time Interval Example
- A DATE Interval Example
- A Complex Time Interval Example using CAST
- A Complex Time Interval Example using CAST
- The OVERLAPS Command
- An OVERLAPS Example that Returns No Rows
- The OVERLAPS Command using TIME
- The OVERLAPS Command using a NULL Value
- Chapter 13 – OLAP Functions
- CSUM
- CSUM – The Sort Explained
- CSUM – Rows Unbounded Preceding Explained
- CSUM – Making Sense of the Data
- CSUM – Making Even More Sense of the Data
- CSUM – The Major and Minor Sort Key(s)
- Reset with a PARTITION BY Statement
- PARTITION BY only Resets a Single OLAP not ALL of them
- ANSI Moving Window is Current Row and Preceding n Rows
- How ANSI Moving SUM Handles the Sort
- Quiz – How is that Total Calculated?
- Answer to Quiz – How is that Total Calculated?
- Moving SUM every 3-rows Vs a Continuous Average
- Partition by Resets an ANSI OLAP
- Moving Average
- The Moving Window is Current Row and Preceding
- How Moving Average Handles the Sort
- Quiz – How is that Total Calculated?
- Answer to Quiz – How is that Total Calculated?
- Quiz – How is that 4th Row Calculated?
- Answer to Quiz – How is that 4th Row Calculated?
- Moving Average every 3-rows Vs a Continuous Average
- Partition By Resets an ANSI OLAP
- RANK Defaults to Ascending Order
- Getting RANK to Sort in DESC Order
- RANK() OVER and PARTITION BY
- RANK() OVER And LIMIT
- PERCENT_RANK() OVER
- PERCENT_RANK() OVER with 14 rows in Calculation
- PERCENT_RANK() OVER with 21 rows in Calculation
- Quiz – What Causes the Product_ID to Reset?
- Answer to Quiz – What Cause the Product_ID to Reset?
- COUNT OVER for a Sequential Number
- Quiz – What caused the COUNT OVER to Reset?
- Answer to Quiz – What caused the COUNT OVER to Reset?
- The MAX OVER Command
- MAX OVER with PARTITION BY Reset
- The MIN OVER Command
- Quiz – Fill in the Blank
- Answer – Fill in the Blank
- The Row_Number Command
- Quiz – How did the Row_Number Reset?
- Quiz – How did the Row_Number Reset?
- Standard Deviation Functions Using STDDEV / OVER
- Standard Deviation Functions and STDDEV / OVER Syntax
- STDDEV / OVER Example
- VARIANCE / OVER Syntax
- Variance Functions Using VARIANCE / OVER
- Using VARIANCE with PARTITION BY Example
- Using FIRST_VALUE and LAST_VALUE
- Using FIRST_VALUE
- Using LAST_VALUE
- Using LAG and LEAD
- Using LEAD
- Using LEAD With and Offset of 2
- Using LAG
- Using LAG with an Offset of 2
- Chapter 14 – Temporary Tables
- CREATING A Derived Table
- The Three Components of a Derived Table
- Naming the Derived Table
- Aliasing the Column Names in The Derived Table
- Visualize This Derived Table
- Most Derived Tables Are Used To Join To Other Tables
- Multiple Ways to Alias the Columns in a Derived Table
- Our Join Example with a Different Column Aliasing Style
- Column Aliasing Can Default for Normal Columns
- CREATING a Derived Table using the WITH Command
- Our Join Example With the WITH Syntax
- WITH
- A WITH Clause That Produces Two Tables
- The Same Derived Query shown Three Different Ways
- Quiz - Answer the Questions
- Answer to Quiz - Answer the Questions
- Clever Tricks on Aliasing Columns in a Derived Table
- A Derived Table lives only for the lifetime of a single query
- An Example of Two Derived Tables in a Single Query
- Create Table Syntax
- Basic Temporary Table Examples
- More Advanced Temporary Table Examples
- Advanced Temporary Table Examples
- Performing a Deep Copy
- Deep Copy Using the Original DDL
- Deep Copy Using A CTAS
- Deep Copy Using A Create Table LIKE
- Deep Copy by Creating a Temp Table and Truncating Original
- Chapter 15 – Sub-query Functions
- An IN List is much like a Subquery
- An IN List Never has Duplicates – Just like a Subquery
- An IN List Ignores Duplicates
- The Subquery
- The Three Steps of How a Basic Subquery Works
- These are Equivalent Queries
- The Final Answer Set from the Subquery
- Quiz- Answer the Difficult Question
- Answer to Quiz- Answer the Difficult Question
- Should you use a Subquery or a Join?
- Quiz- Write the Subquery
- Answer to Quiz- Write the Subquery
- Quiz- Write the More Difficult Subquery
- Answer to Quiz- Write the More Difficult Subquery
- Quiz- Write the Subquery with an Aggregate
- Answer to Quiz- Write the Subquery with an Aggregate
- Quiz- Write the Correlated Subquery
- Answer to Quiz- Write the Correlated Subquery
- The Basics of a Correlated Subquery
- The Top Query always runs first in a Correlated Subquery
- Correlated Subquery Example vs. a Join with a Derived Table
- Quiz- A Second Chance to Write a Correlated Subquery
- Answer - A Second Chance to Write a Correlated Subquery
- Quiz- A Third Chance to Write a Correlated Subquery
- Answer - A Third Chance to Write a Correlated Subquery
- Quiz- Last Chance to Write a Correlated Subquery
- Answer – Last Chance to Write a Correlated Subquery
- Quiz- Write the NOT Subquery
- Answer to Quiz- Write the NOT Subquery
- Quiz- Write the Subquery using a WHERE Clause
- Answer - Write the Subquery using a WHERE Clause
- Quiz- Write the Subquery with Two Parameters
- Answer to Quiz- Write the Subquery with Two Parameters
- How the Double Parameter Subquery Works
- More on how the Double Parameter Subquery Works
- Quiz – Write the Triple Subquery
- Answer to Quiz – Write the Triple Subquery
- Quiz – How many rows return on a NOT IN with a NULL?
- Answer – How many rows return on a NOT IN with a NULL?
- How to handle a NOT IN with Potential NULL Values
- Using a Correlated Exists
- How a Correlated Exists matches up
- The Correlated NOT Exists
- The Correlated NOT Exists Answer Set
- Quiz – How many rows come back from this NOT Exists?
- Answer – How many rows come back from this NOT Exists?
- Chapter 16 – Substrings and Positioning Functions
- The TRIM Command trims both Leading and Trailing Spaces
- A Visual of the TRIM Command Using Concatenation
- Trim and Trailing is Case Sensitive
- How to TRIM Trailing Letters
- The SUBSTRING Command
- How SUBSTRING Works with NO ENDING POSITION
- Using SUBSTRING to move Backwards
- How SUBSTRING Works with a Starting Position of -1
- How SUBSTRING Works with an Ending Position of 0
- The POSITION Command finds a Letters Position
- Quiz – Find that SUBSTRING Starting Position
- Answer to Quiz – Find that SUBSTRING Starting Position
- Using the SUBSTRING to Find the Second Word On
- Quiz – Why did only one Row Return?
- Answer to Quiz – Why Did only one Row Return?
- Concatenation
- Concatenation and SUBSTRING
- Four Concatenations Together
- Troubleshooting Concatenation
- Declaring a Cursor
- Chapter 17 – Interrogating the Data
- Quiz – What would the Answer be?
- Answer to Quiz – What would the Answer be?
- The NULLIF Command
- Quiz – Fill in the Blank Values in the Answer Set
- Answer to Quiz – Fill in the Blank Values in the Answer Set
- Quiz – Fill in the Answers for the NULLIF Command
- Quiz – Fill in the Answers for the NULLIF Command
- Quiz – Fill in the Answers for the NULLIF Command
- Quiz – Fill in the Answers for the NULLIF Command
- The ISNULL, NVL and COALESCE Commands
- The ISNULL, NVL and COALESCE Commands
- The ISNULL, NVL and COALESCE more examples
- The COALESCE Answer Set
- The Coalesce Quiz
- Answer – The Coalesce Quiz
- The Basics of CAST (Convert And STore)
- Some Great CAST (Convert And STore) Examples
- Some Great CAST (Convert And STore) Examples
- Some Great CAST (Convert And STore) Examples
- The Basics of the CASE Statements
- The Basics of the CASE Statement
- Valued Case Vs. A Searched Case
- Quiz - Valued Case Statement
- Answer - Valued Case Statement
- Quiz - Searched CASE Statement
- Answer - Searched CASE Statement
- Quiz - When NO ELSE is present in CASE Statement
- Answer - When NO ELSE is present in CASE Statement
- When an ELSE is present in CASE Statement
- Answer - When an ELSE is present in CASE Statement
- When an Alias is NOT used in a CASE Statement
- Answer - When an Alias is NOT used in a CASE Statement
- Combining Searched Case and Valued Case
- Nested Case
- Put a CASE in the ORDER BY
- Chapter 18 – View Functions
- Creating a Simple View to Restrict Sensitive Columns
- Creating a Simple View to Restrict Rows
- Creating a View to Join Tables Together
- You Select From a View
- Basic Rules for Views
- An ORDER BY Example Inside of a View
- An ORDER BY Inside of a View that is Queried Differently
- Creating a View with Ordered Analytics
- Creating a View with the TOP Command
- Creating a View with the LIMIT Command
- Altering a Table
- Altering a Table after a View has been Created
- A View that Errors after An ALTER
- Troubleshooting a View
- Updating Data in a Table through a View
- Chapter 19 – Set Operators Functions
- Rules of Set Operators
- INTERSECT Explained Logically
- INTERSECT Explained Logically
- UNION Explained Logically
- UNION Explained Logically
- UNION ALL Explained Logically
- UNION Explained Logically
- EXCEPT Explained Logically
- EXCEPT Explained Logically
- Minus Explained Logically
- Minus Explained Logically
- Testing Your Knowledge
- Answer - Testing Your Knowledge
- Testing Your Knowledge
- Answer - Testing Your Knowledge
- An Equal Amount of Columns in both SELECT List
- Columns in the SELECT list should be from the same Domain
- The Top Query handles all Aliases
- The Bottom Query does the ORDER BY (a Number)
- Great Trick: Place your Set Operator in a Derived Table
- UNION Vs UNION ALL
- A Great Example of how EXCEPT works
- Chapter 20 – Statistical Aggregate Functions
- Chapter 21 – Nexus
- Nexus is Now Available on the Microsoft Azure Cloud
- Nexus Queries Every Major System
- Setup of Nexus is as easy as pie
- Setup of Nexus is a Easy as 1, 2, 3
- Nexus Data Visualization
- Nexus Data Visualization
- Nexus Data Visualization Shows What Tables Can Be Joined
- Nexus is doing a Five-Table Join
- Nexus Generates the SQL Automatically
- Nexus Delivers the Report
- Cross-System Joins from Teradata, Oracle and SQL Server
- The Tab of the Super Join Builder
- The 9 Tabs of the Super Join Builder – Objects Tab 1
- Selecting Columns in the Objects Tab
- The 9 Tabs of the Super Join Builder – Columns Tab 2
- Removing Columns from the Report in the Columns Tab
- The 9 Tabs of the Super Join Builder – Sorting Tab 3
- The 9 Tabs of the Super Join Builder – Joins Tab 4
- The 9 Tabs of the Super Join Builder – Where Tab 5
- Using the WHERE Tab For Additional WHERE or AND
- The 9 Tabs of the Super Join Builder – SQL Tab 6
- The 9 Tabs of the Super Join Builder – Answer Set Tab 7
- The 9 Tabs of the Super Join Builder – Analytics Tab 9
- Analytics Tab
- Analytics Tab – OLAP Example
- Analytics Tab – OLAP Example of SQL Generated
- Analytics Tab – Grouping Sets Example
- Analytics Tab – Grouping Sets Answer Set
- Nexus Data Movement
- Moving a Single Table To a Different System
- The Single Table Data Movement Screen
- Moving an Entire Database To a Different System
- The Database Mover Screen
- The Database Mover Options Tab
- Converting DDL Table Structures
- Converting DDL Table Structures
- Converting DDL Table Structures
- Hound Dog Compression
- Hound Dog Compression on Teradata
- Hound Dog Compression on Teradata
Chapter 9 – Distinct Vs Group By AND TOP
“A bird does not sing because it has the answers, it sings because it has a song.”
- Anonymous
The Distinct Command
DISTINCT eliminates duplicates from returning in the Answer Set.
Distinct vs. GROUP BY
Both examples produce the exact same result
Class_Code
FR
JR
SO
SR
?
Rules for Distinct Vs. GROUP BY
(1) Many Duplicates – use GROUP BY
(2) Few Duplicates – use DISTINCT
(3) Space Exceeded - use GROUP BY
Distinct and GROUP BY in the two examples return the same answer set.
Quiz – How many rows come back from the Distinct?
How many rows will come back from the above SQL?
Answer – How many rows come back from the Distinct?
SELECT Distinct Class_Code, Grade_Pt
FROM Student_Table
ORDER BY Class_Code, Grade_Pt ;
How many rows will come back from the above SQL? 10. All rows came back. Why? Because there are no exact duplicates that contain a duplicate Class_Code and Duplicate Grade_Pt combined. Each row in the SELECT list is distinct.
TOP Command
In the above example, we brought back 3 rows only. This is because of the TOP 3 statement which means to get an answer set, and then bring back the first 3 rows in that answer set. Because this example does not have an ORDER BY statement, you can consider this example as merely bringing back 3 random rows.
TOP Command is brilliant when ORDER BY is Used!
In the above example, we brought back 3 rows only. This is because of the TOP 3 statement which means to get an answer set, and then bring back the first 3 rows. Because this example uses an ORDER BY statement, the data brought back is from the top 3 students with the highest Grade_Pt. This is the real power of the TOP command. Use it with an ORDER BY!
What is the Difference between TOP and LIMIT?
Both queries above bring back the top 3 students with the highest grade_pt. The TOP command is designed to bring back the top n rows. The LIMIT clause is used more often if you merely want to see a quick sample, but both techniques will work with an ORDER BY statement and both can utilize an ORDER BY statement in the creation of a view.
-
No Comment
..................Content has been hidden....................
You can't read the all page of ebook, please click here login for view all page.