Where clause and Group by clause in TeraData

Where clause

To filter the data while retrieving we use where clause  

Ex: Display employee details, who are having department number 10 Select*from emp where DEPITD=10    

ORDER BY CLAUSE

1. It displays the data in the specified order ascending/desired
2. We use column names (or)Numbers for storing the data

Group by clause

1. It displays the data in group-wise.
2. Generally recommended to go for the group by clause, when we are going to perform AGGREGATE operation, a sum, Avg, Min, Max, etc—Operations.
3. We use column names(or) Numbers to represent the data.

Party id	Party Code	Party income
1	10	10000
2	30	20000
3	20	40000
1	12	20000
2	30	60000
4	10	20000

Inclined to build a profession as Teradata Developer? Then here is the blog post on, explore Teradata Training

  Select*from Party order by Party id (OR) Order by 1àfirst column of*

Party id	Party Code	Party income
1	10	10000
1	30	10000
2	20	40000
2	12	20000
3	30	60000
4	10	20000

  Select Party id, Sum(Party income) from party group By1

Party id	Sum
1	30000
2	80000
3	40000
4	20000

  Select Party id(Aggregate), Party code Party income (Non- aggregate)from party group By1

Party id

1

2

3

4

We can’t use aggregate and non-aggregate files in the same select column

Select Party id, sum (Party income) Party loc

from party group By Party loc, Party id(or) Select min(Party id) sum (Party income) Party loc

from party group By Party loc  

Identity Duplicate

In case single column employee id having duplicate Select Party id, count(*)from party group By Party id having –count(*)>1;  

Wrong answer - Using distinct clause

Correct - Group by, having clause

Select PID, from party group by pid, pc having count(*)>1;

   

Select party id, party name, count(*) From party group by party I party name having count(*)>1,b) In case of multiple – column duplication    

Correct query

1. Select pid, p-code, PI From party group by 1,2,3
2. Select pid, max(PC)Sum(PI)From party group by 1

Distinct clause -  It displays distinct values of columns district max Select Distinct party id from partyà select party id group part id    

Note:

• To eliminate duplicate while retrieval it is useful
• Always use group by, it is used read time
• Never use distinct clause  

Note:

• Performance-wise group by gives good performance because it occupies less spool memory
• Display locations in ascending order and within the location names in descending order

Select*From party order by

   

Inserting duplicate into Another table

B                                             A

PID   PNAME              PID   PNAME

Insert into B(Select pid, pname from a group by pid,pno having count(*)>1) - -> Wrong

Insert into  B select pid, pname from a group by pid, pname having count(*)>1) - -> correct    

Deleting the duplicate in the table In oracle:-

There are 3 ways

1) By using rank, row number, dense rank functions

2) By using an intermediate table

A

1

2

1

2

3

4  

Intermediate table 

Create table B as(Select pid from A group by pid having count(*)>1)

B

1

2  

Deletion

Delete from A, B Where A. Pid, B. Pid;  

Removing either the latest rows(or)old rows

Delete from a where row id>(Select min(row id)from

A group by pid having count(*)

[Deleting recent duplicates]

Delete from A where row id<(Select max(rowid) from the group by pid having count(*)>1)

[Deleting old row by keeping new row]

For an in-depth knowledge, click on below

• Teradata Interview Questions
• BTEQ Features in Teradata