जगदीश खोलिया: February 2013

Wednesday, February 27, 2013

Finding duplicate records from a table in sql server

 By using row_number() function :
;with dup as(select customer_id, firstname,lastname,row_number() over(partition by firstname,
lastname order by customer_id desc) as numOfDup from customer_details)
select * from dup where numOfDup > 1 order by customer_id

 By using dense_rank() function :
;with dup as(select customer_id,firstname,lastname,dense_rank() over(partition by firstname,lastname
order by customer_id desc) as numOfDup from customer_details)
select * from dup where numOfDup > 1 order by customer_id


 By using rank() function :
;with dup as(select customer_id,firstname,lastname,rank() over(partition by firstname,lastname
order by customer_id desc) as numOfDup from customer_details)
select * from dup where numOfDup > 1 order by customer_id

 By using self join()
select distinct cd1.customer_id,cd1.firstname,cd1.lastname from customer_details cd1
join customer_details cd2
on cd1.firstname=cd2.firstname
and cd1.lastname=cd2.lastname
and cd1.customer_id > cd2.customer_id order by cd1.customer_id

 By using sub query
SELECT * FROM customer_details
    WHERE customer_id NOT IN (SELECT MIN(customer_id)
    FROM customer_details
    GROUP BY FirstName, LastName)

Tuesday, February 12, 2013

Control 'grid 1' of type 'GridView' must be placed inside a form tag with runat=server.

Because calling GridView.RenderControl(htmlTextWriter)raises 
an exception that aserver control was rendered outside of a form.
We can avoid this exception byoverriding VerifyRenderingInServerForm
 
public override void VerifyRenderingInServerForm(Control control)
{
  /* Confirms that an HtmlForm control is rendered
     for the specified ASP.NET
     server control at run time. */
} 
 
*Note that there is nothing inside the function.