Partition Or Grouping By in SQL Server

     Partition Or Grouping By:

To display category wise  information .

This partition is useful for

a)      SELECt PartyID,PartyIncome,Rank() over (Orger by PartyIncome DESC)

FROM Party

b)      SELECT PartID,PartyIncome,Dense_Rank() Over(Order by PartyIncome DESC)

FROM Party

c)       SELECT PartyID,PartyIncome,Row_Number over (Order by PartyIncome DESC)

FROM Party

d)      SELECT PartyID,PartyIncome,NTile(9) over

(order by PartyIncome DESC) FROM Party

Distributes 9 Ranks across all rows

Assume 40 records è40/9=4 reminder

1è4è4+1

2è4è4+1

3è4è4+1

4è4è4=1

5è4

SQL Server 2005 New Analytical Functions?

SQL Server 2005 new analytical functions?

1.       Rank:

A)     Display rank for every column value

B)      It manintain gap between ranks

Syntax:

RANK() over (partion By<Columns>

              ORDER by<Columns>)

2.       Dense_Rank:

A)     Takes a rank for every column without gap.

Syntax:

Dense_Rank() over (partition By <Column>

                           Order By <Column>)

3.       Row_Number:

A)     Takes a sequence number for every column.

Syntax:

Row_Number() over (partition By <column>

                              ORDER by <Column>)

4.       NTile:

A)     Distributes the row according to the integer expression specified .

Syntax:

NTile(integer expression) over (partition By <column>

                                               ORDER by <column>)

5.       Pivot:

6.       UNPivot:

How do you identify duplicates and how do you eliminates duplicates from the table?

Don’t use distinct query for identifying duplicate values.

Correct way is Group By and Having

SELECT PartyID FROM Party

Group By PartyID

Having Count(*)>1

Elimination: (To remove recent ) record

Delete FROM Test WHERE Test .%%Physloc %%

NOT IN(SELECT min (%%PHysloc %%)

FROM Test Group by TID)

Physloc: It is used to eliminate the duplicates. i.e assigns nuber to every new record.

OR

By using CTE:

WITH CT (TID,Ranking)

AS

(SELECT TID,Ranking = Dense+_Rank() Over

(partition by TID order by NewID())

FROM Test

)

Delete FROM CT WHERE Ranking>)

Important SQL Queries

EID	ENAME	DID	Gender	ELOC
1	Ravi	10	Male	Null
2	Rani	20	Female	Null
3	Naveen	10	Male	Null

 Change the Gender Male to Female or Female to Male in a single query?

UPDATE  emp  set  Gender = CASE  When  Gender=’Male’

Then  ‘Female’

When  Gender=’Female’  Then  Male

End

 Display Ravi details by using the name only without using LIKE operator and JOINS? Without using ‘equals subquery?

SELECT * from Emp Where

ENAME Between ‘Ravi’ AND ‘Ravi’

 Display Names with start V without using LIKE Operator?

SELECT * from Emp WHERE

Charindex(‘V’,ENAME)=1