GMi technologies

Count repetition of rows

use this syntax:-


create table mytable (mytableid int primary key, userid int,counterid int )

insert into mytable values (

1,           1,         4),
(2   ,        1 ,        7),
(3  ,         4  ,       3),
(4,           11,        9),
(5          , 11 ,       3),
(6           ,4   ,      6),
(9           ,11   ,     9)


select * from mytable


main syntax will be:-



;WITH Ordering
AS
(
  SELECT mytableid,       
  userid,       
  counterid,       
 
  ROW_NUMBER() OVER (ORDER BY userid, counterid) RN
  FROM mytable
)
SELECT
  CurrentRow.mytableid,
  CurrentRow.userid,
  CurrentRow.counterid,
 
  CurrentRow.RN -
    ISNULL((SELECT TOP 1 RN FROM Ordering O3 WHERE RN < CurrentRow.RN AND (CurrentRow.mytableid <> O3.mytableid OR CurrentRow.counterid <> O3.counterid OR CurrentRow.userid <> O3.userid) ORDER BY RN DESC), 0) Repetitions
FROM Ordering CurrentRow
LEFT JOIN Ordering O2 ON CurrentRow.RN + 1 = O2.RN
WHERE O2.RN IS NULL OR (CurrentRow.mytableid <> O2.mytableid OR CurrentRow.counterid <> O2.counterid OR CurrentRow.userid <> O2.userid )
ORDER BY CurrentRow.RN

using table twice in select stmt

i've been asked a question like this:-

table structure is:

mytableid int pk
userid int
counterid int


in this table he has some data like this:

MyTableID   UserID   Counter
---------   -------  --------
1           1         4
2           1         7
3           4         3
4           11        9
5           11        3
6           4         6
...
9           11        9


now his query is, he wants use a select stmt which gives the result with max counterid and the userid should not repeat.......

so many peoples are posted so many syntaxes.........but he is not satisfied with their syntaxes.....

then...i gave the solution like this

select distinct(a.userid),
MAX(a.counterid) as counterid
 from mytable a, mytable b
where a.mytableid=b.mytableid group by a.userid





ff
f
finally it works............


these all are done in famous site WWW.STACKOVERFLOW.COM

shortkuts

shortkut for opening sql server ....type SSMS on run command...it is only applicable in server 2008

CTRL+U for changing of database option

ALT+N  for switch over to new query window

CTRL+R  for minimizing result window



njoy sql programming.....i hope these shortkuts will be useful for many of u......

my first achievement through microsoft

Microsoft Certifications

hai.frnds........

i've earned Microsoft Certification Yeaster day on my first attempt.......exam expereience was awesome....was very much tensed...to view my certification ..visit https://mcp.microsoft.com/authenticate/validatemcp.aspx . Transcript code is 997943 AND access code is CHINNA7485

CREATING FILE GROUPS

hello  guys........
simple syntax for making of file groups in sql

create database test on primary
(NAME=TESTFG, FILENAME='D:\TEST.MDF', SIZE=500MB,MAXSIZE=700, FILEGROWTTH=20)



ALTER DATABASE TEST ON FILEGROUP TEST1
(NAME=TESTPTIMARY, FILENAME='D:\NDF', SIZE=700MB, MAXSIZE=900, FILEGROWTH=20)

V I SIVA RAO: Auto Updation--Merge Statement

V I SIVA RAO: Auto Updation--Merge Statement: The syntax is here:- merge itemorders as d using (select itemid,sum(b.qtyissued)as issuedqty from sales a, salesdetails b where a.orderno=...

Auto Updation--Merge Statement

The syntax is here:-
merge itemorders as d using (select itemid,sum(b.qtyissued)as issuedqty from sales a, salesdetails b where
a.orderno=b.orderno and a.orderid=11 and dbo.dateonly(a.orderdate)>'2011/12/1' group by itemid)c
on d.itemid=c.itemid
when matched and d.qty-c.orderqty<=0
then delete
when matched
then update set d.qty=d.qty-c.orderty
output $action,inserted.qty,inserted.itemid,deleted.qty,deleted.itemid;







-------------------
It will automatically update the stock of Itemorders Table

merge statement

syntax here:-

merge itemorders as d using (select itemid,sum(b.qtyissued)as issuedqty from sales a, salesdetails b where
a.orderno=b.orderno and a.orderid=11 and dbo.dateonly(a.orderdate)>'2011/12/1' group by itemid)c
on d.itemid=c.itemid
when matched and d.qty-c.orderqty<=0
then delete
when matched
then update set d.qty=d.qty-c.orderty
output $action,inserted.qty,inserted.itemid,deleted.qty,deleted.itemid;

how to know index fragmentation?

 pre requisites:-

create table fragmentation (schemaname varchar(250), tablename varchar(50),indexname varchar(50),fragmentation float)


syntax is here:-




declare @schemaname varchar(250),
@tablename varchar(250),
@indexname varchar(250),
@fragmentation float
begin
declare fragmentation cursor local for select schema_name(cast(objectpropertyex(a.object_id,'schema_id')as int)),object_name(a.object_id),a.name, b.avg_fragmentation_in_percent FROM sys.indexes a join sys.dm_db_index_physical_stats(db_id(),null,null,null,null) b on a.object_id=b.object_id and a.index_id=b.index_id where avg_fragmentation_in_percent>30
open fragmentation
fetch next from fragmentation into @schemaname,@tablename,@indexname,@fragmentation
while @@FETCH_STATUS=0
begin

insert into fragmentation values (@schemaname,@tablename,@indexname,@fragmentation)
fetch next from fragmentation into @schemaname,@tablename,@indexname,@fragmentation

end

end
close fragmentation
deallocate fragmentation
select * from fragmentation

earn money from home

are you interested to work from home......i will gove u one of the finest place to do so.....

http://www.sbrservices.in/ .......unbeleivabale services @ cheap rates...........no hidden charges......no registration fees.....get 3-10% discount in all recharges(mobile, DTH, hotels flights....and many more

THOUGH FOR THE DAY

BE PASSIONATE ABOUT UR WORK, TONNES OF KNOWLEDGE WILL AUTOMATICALLY COME....

how to delete indexes at a time?

the syntax is

declare @qry nvarchar(max);
select @qry =
(SELECT  'DROP INDEX ' + ix.name + ' ON ' + OBJECT_NAME(ID) + '; '
FROM  sysindexes ix
WHERE   ix.Name IS NOT null and ix.Name like '%prefix_%'
for xml path(''));
exec sp_executesql @qry

how to delete constraints?

here am giving the syntax to delete the constraints

Declare @TABLENAME varchar(max), @COLUMN varchar(max)
SET @TABLENAME = 'YOURTableName'
SET @COLUMN = 'YOURColumnName'
Declare @CONSTRAINT varchar(max)
                    set @CONSTRAINT ='ALTER TABLE '+@TABLENAME+' DROP CONSTRAINT '
                    set @CONSTRAINT = @CONSTRAINT + (select SYS_OBJ.name as CONSTRAINT_NAME
                    from sysobjects SYS_OBJ
                    join syscomments SYS_COM on SYS_OBJ.id = SYS_COM.id
                    join sysobjects SYS_OBJx on SYS_OBJ.parent_obj = SYS_OBJx.id
                    join sysconstraints SYS_CON on SYS_OBJ.id = SYS_CON.constid
                    join syscolumns SYS_COL on SYS_OBJx.id = SYS_COL.id
                    and SYS_CON.colid = SYS_COL.colid
                    where
                    SYS_OBJ.uid = user_id() and SYS_OBJ.xtype = 'D'
                    and SYS_OBJx.name=@TABLENAME and SYS_COL.name=@COLUMN)
                    exec(@CONSTRAINT)
--alter table <your tblename> drop column <your column name>