Different ways to find TOP N records.

For our explanation we have taken a table ‘parent_tab ‘ having the following seven records data.

select * from parent_tab

first_id    name

----------- --------------------------------------------------

1           Aadarshini

2           Taksha

3           Tanika

4           Vaikunth

5           Ekantika

6           Gargi

7           Sachiv

(7 row(s) affected)

Various ways to achieve this : Our objective is to find first 4 records from the above table

1. Using ROWCOUNT

set rowcount 4

select name from parent_tab

set rowcount 0

name

--------------------------------------------------

Aadarshini

Taksha

Tanika

Vaikunth

(4 row(s) affected)

2. Using ROW_NUMBER

select name from

(

select row_number() over (order by first_id) as rno, name from parent_tab

) as tab where rno <= 4

name

--------------------------------------------------

Aadarshini

Taksha

Tanika

Vaikunth

(4 row(s) affected)

3. Using Top N

select top 4 name from parent_tab

name

--------------------------------------------------

Aadarshini

Taksha

Tanika

Vaikunth

(4 row(s) affected)

If you know any other way to achieve this, please light up your suggestion to me.

Post Reference: Vikram Aristocratic Elfin Share

TOP, WITH TIES and ORDER BY? What is WITH TIES clause in SQL Server?

WITH TIES can be used only when TOP and ORDER BY clauses are present in SELECT statement, both the clauses are required in order to use WITH TIES.

Let understand this by taking a small example: Here we are having a table parent_tab with following records:

select * from parent_tab

first_id    name

----------- --------------------------------------------------

1           sneha

3           pratik

3           pratik

1           sneha

4           chitrangada

5           chitrangada

(6 row(s) affected)

Now I am firing a select query with Top clause to retrieve the topmost record from the table [parent_tab] where the name is equal to ‘chitrangada’.

select top 1  * from parent_tab where name like 'chitrangada'

first_id    name

----------- --------------------------------------------------

4           chitrangada

(1 row(s) affected)

Here we can see the top most record has been arrived by the above query.

Now If I want to retrieve the top most record and all the records in the table where the name is equal to ‘chitrangada’?

If  such is my requirement then ‘WITH TIES’ will help you out, let see how:

select top 1 with ties  * from parent_tab where name like 'chitrangada' order by name

first_id    name

----------- --------------------------------------------------

4           chitrangada

5           chitrangada

(2 row(s) affected)

What it does it when you use TOP 1 rows, it will return you only 1 rows, but when used TOP 1 WITH TIES, it will return you all the rows that have same value as that of the last record of TOP 1.

The expected result is based on the column that is specified in ORDER BY. That is it will look for the column used in the ORDER BY to compare its equivalent in rest of the table.

NOTE: WITH TIES Clause can be used only with TOP and ORDER BY, both the clauses are required.

Post Reference: Vikram Aristocratic Elfin Share

How to find Distinct record using GROUP BY?

How to find distinct records using group by? Then what is the difference between group by and Distinct?

Taking a scenario where we are having a table with duplicate records like the one mentioned below:

select first_id,name from vw_parent_tab

first_id    name

----------- -----------------------------------------

1           sneha

3           pratik

3           pratik

1           sneha

(4 row(s) affected)

Now if we want to find distinct record we can easily do it with the help of distinct clause

select distinct first_id,name from vw_parent_tab

first_id    name

----------- ------------------------------------------

1           sneha

3           pratik

(2 row(s) affected)

But the question is how to find the same distinct result using GROUP BY Clause; Answer is simple add a group by clause and put all the field you want to display using select query with the Group By.

select first_id,name from vw_parent_tab group by first_id,name

first_id    name

----------- -----------------------------------------

1           sneha

3           pratik

(2 row(s) affected)

If you are using a group by without any aggregate function then internally it will be treated as Distinct so in this case there is no difference between group by and Distinct...
But when you are provided with Distinct Clause better to use it for finding your unique records because objective of group by is to achieve aggregation not distinct.

Use of Group by:

select first_id,name,count(*) as 'duplicate_count' from vw_parent_tab group by first_id,name

first_id    name                       duplicate_count

----------- -------------------------- ---------------

3           pratik                     2

1           sneha                      2

(2 row(s) affected)

A hammer can work to drive in a screw sometimes, but if you have got a screwdriver handy, why bother?

Conclusion:  Use Distinct when you want distinct records whereas use group by when you want your records to be aggregated.

Post Reference: Vikram Aristocratic Elfin Share

How DATETIME2 differs with DATETIME?

If you are having a table with two different columns of datetime datatype as FirstModified and LastModified. And you have datatype for FirstModified as Datetime and LastModified as Datetime2.

create table testDemoForDateDatatype

(

firstName varchar(50),

firstModified datetime,

lastModified datetime2,

)

Now when you are populating each of them with SYSDATETIME, you assume that the value inserted in the table will be the same, you have done on insert operation on it, no update operation has yet done on the table.

insert into testDemoForDateDatatype values('abc', sysdatetime() ,sysdatetime ())

insert into testDemoForDateDatatype values('xyz', sysdatetime (),sysdatetime ())

insert into testDemoForDateDatatype values('def', sysdatetime (),sysdatetime ())

But when you querying the table with distinct clause on both these columns and you will be surprised by the result because your perception will be like both the column will have the same data, but in fact, they had very different data.

The date value in the DATETIME field gets rounded up whereas the value didn’t round up in the field having DATETIME2 datatype.

*The best way is to use GETDATE () if you are using DATETIME datatype.

*And SYSDATETIME () if you are using DATETIME2

 select getdate() -- 2011-12-28 11:23:39.727

select sysdatetime () --2011-12-28 11:23:39.9062500

NOTE: DATETIME2 and Sysdatetime() was introduced in SQL Server 2008. So try this in 2008 SQL Server.

Post Reference: Vikram Aristocratic Elfin Share

DELETE BY JOINING TABLES

Join in DELETE CLAUSE :  

There are situation where we need to delete records based on one or other condition and that in turn depends on joining of tables.

We start wondering whether DELETE statement hold joins??

Yes you can use join with delete statement, let’s have a small practical.

I am having two table ‘PARENT_TAB’ and ‘CHILD_TAB’ where ‘first_id’ is the Primary key in PARENT_TAB and same is the Foreign Key in CHILD_TAB.

The data in the table are as follows

select * from parent_tab

first_id    name

----------- --------------------------------

2           nimesh

3           pratik

(2 row(s) affected)

select * from child_tab

second_id   first_id    dept

----------- ----------- ----------------

3           2           DEVLOPMENT

5           2           DESIGNING

(2 row(s) affected)

Q Now the question is how to delete record using join in delete statement from CHILD_TAB where the candidate name is ‘nimesh’.

delete parent_tab

from parent_tab p, child_tab c

where p.first_id=c.first_id and p.name='nimesh'

It is simple as writing select statement, add FROM clause give the table name you wanna join with alias and then in WHERE clause join the table and put the condition.

After executing above DELETE statement, execute the following statement

select * from child_tab

second_id   first_id    dept

----------- ----------- --------------------------------------------------

(0 row(s) affected)

Since both the record belongs to candidate ‘nimesh’ both get deleted.

 Post Reference: Vikram Aristocratic Elfin Share

CASCADE IT (UPDATE / DELETE)

Many a time we need to clean up all the table data. For this we have first traverse through each child table and delete data gradually from bottom to top.

This is time consuming, instead if we alter table to add ON DELETE CASCADE clause to the reference key field then by deleting master table record the corresponding child record will get deleted.

ALTER TABLE [dbo].[CHILD_TAB] ADD  CONSTRAINT [FK_CHILD_TAB_PARENT_TAB] FOREIGN KEY([first_id])

REFERENCES [dbo].[PARENT_TAB] ([first_id])

ON UPDATE CASCADE

ON DELETE CASCADE

Same way if we want modifying master record modify the related child record then we can alter table to have ON UPDATE CLAUSE to reference key field.

EXAMPLE: Here in this example code we are having Child table as ‘CHILD_TAB’ and Parent table as ‘PARENT_TAB’ and we are making first_id of ‘CHILD_TAB’  as foreign key field to the Primary Key field first_id of ‘PARENT_TAB’ with ON UPDATE and ON DELETE CASCADE.

update parent_tab

set first_id = 1 where first_id=5

Now when we update first_id of ‘PARENT_TAB’ automatically all the child record will get updated.

delete parent_tab where first_id=1

Similirly when we delete ‘PARENT_TAB’ where first_id=1, it automatically delete all record in child table ‘CHILD_TAB’ with first_id=1

Post Reference: Vikram Aristocratic Elfin Share