Thursday, February 28, 2013

USE SUBQUERY IN INNER JOIN


create table tblUser
 (
  UserId int identity(1,1) not null,
  UserName nvarchar(500),
  Age int

 )

 INSERT INTO tblUser select 'Rahul',26
 INSERT INTO tblUser select 'Yunus',26


 create table tblTransaction

  (
   TransactionId int identity(1,1) not null,
   UserId int,
   Amount int ,
   Status bit
  )

  INSERT INTO tblTransaction SELECT 1,200,1
  INSERT INTO tblTransaction SELECT 1,2100,1
  INSERT INTO tblTransaction SELECT 1,900,1
  INSERT INTO tblTransaction SELECT 1,60,1



  INSERT INTO tblTransaction SELECT 2,200,1
  INSERT INTO tblTransaction SELECT 2,2100,1
  INSERT INTO tblTransaction SELECT 2,900,1
  INSERT INTO tblTransaction SELECT 2,60,1
  INSERT INTO tblTransaction SELECT 2,55,1




SELECT * FROM tblUser
SELECT * FROM tblTransaction

SELECT * FROM tblUser as U
INNER JOIN
(
SELECT * FROM tblTransaction
WHERE
       TransactionId IN
               (
                       SELECT MAX(TransactionId)
                       FROM
                               tblTransaction
                       GROUP BY UserId
               )
) AS [A]
ON
U.UserId=A.UserId

Wednesday, January 23, 2013

Use string type comma separated in IN clause in sql

 declare @ProductIds nvarchar(50)='18,19'
SELECT * FROM products Where (',' + @ProductIds +',' LIKE '%,' + CONVERT(VARCHAR, ProductId) + ',%')

Wednesday, December 26, 2012

Total size of an index or primrary key cannot exceed 900 bytes


When we are making primrary key of a multiple columns , then normally we can face this limit of sql server. Thats fixed and hard limit. You have to simply reduce the size of your columns on which you are creating primrary key .

if you ask why ?  I also dont know why this type of limits are used in MS  SQL SERVER .

Thursday, November 8, 2012

REMOVE DUPLICATE ROWS FROM A TABLE USING SQL

SQL FOR REMOVING DUPLICATE


create table demo(
  id int identity(1,1) not null,
  alpha nvarchar(50)
 
)
insert into demo select 'A'
insert into demo select 'A'
insert into demo select 'A'
insert into demo select 'A'
insert into demo select 'A'


select * from demo

;WITH cte
     AS (SELECT ROW_NUMBER() OVER (PARTITION BY alpha
                                       ORDER BY ( SELECT 0)) RN
         FROM   demo)
DELETE FROM cte
WHERE  RN > 1


select * from demo

update table from another table or same table using inner join


declare @temp table
 (
  Id int identity(1,1) not null,
  alpha nvarchar(50)

 )

 insert @temp  select null
 insert @temp  select null
 insert @temp  select null

 select * from @temp

declare @temp1 table
 (
  Id int identity(1,1) not null,
  alpha nvarchar(50)
 )


 insert @temp1  select 'A'
 insert @temp1  select 'B'
 insert @temp1  select 'C'

 select * from @temp1


 update @temp   set alpha=t1.alpha
 FROM   @temp as t    inner join  @temp1  as t1 ON t.id=t1.id



 select * from @temp

Saturday, October 20, 2012

How to convert c# array into list type of object

hotel[] hotels= new [] { 10, 20, 10, 34, 113 };


 public hotel[] availableHotels ;

availableHotels=

print month name

simple DATENAME function can be used to print month name 

 select DATENAME(month,getdate())


.net core

 Sure, here are 50 .NET Core architect interview questions along with answers: 1. **What is .NET Core, and how does it differ from the tradi...