1.
Select from <table name A>
where rowid > (select min(rowid) from <table name B>
where A.keyvalues=B.keyvalues);


2.
Delete from <table name A>
where rowid > (select min(rowid) from <table name B>
where A.keyvalues=B.keyvalues);

///////////Cursor for delete duplicate record in 
table//////////

DECLARE @ID int
DECLARE @NAME NVARCHAR(50)
DECLARE @COUNT int 

DECLARE CUR_DELETE CURSOR FOR
SELECT [ID],[NAME],COUNT([ID]) FROM [Example] GROUP BY [ID],
[NAME] HAVING COUNT([ID]) > 1 

OPEN CUR_DELETE 

FETCH NEXT FROM CUR_DELETE INTO @ID,@NAME,@COUNT
/* Loop through cursor for remaining ID */
WHILE @@FETCH_STATUS = 0
BEGIN 

DELETE TOP(@COUNT -1) FROM [Example] WHERE ID = @ID 

FETCH NEXT FROM CUR_DELETE INTO @ID,@NAME,@COUNT
END 

CLOSE CUR_DELETE
DEALLOCATE CUR_DELETE 

Mohit D Jethva

delete from employee 
where Emp_id not in (select min(Emp_id) from employee 
group by Emp_Name)

/* selecting duplicate rows in a table */
select col1, col2, ..., colN, count(*)
from TableName
group by col1, col2, ..., colN
having count(*) > 1

/* deleting duplicate rows from a table */
select col1, col2, ..., colN, count(*) as "Duplicates"
into #duplicates
from TableName
group by col1, col2, ..., colN
having count(*) > 1

delete TableName
from TableName t, #duplicates d
where t.col1 = d.col1 and
  ....
      t.colN = d.colN

/* damn I'm good! */

drop table #TEMP select distinct * into #TEMP from 
TABLE_NAME delete from TABLE_NAME insert into TABLE_NAME 
select * from #TEMP

Ans for 1st Query
How to retrieve duplicate rows in a table?

SELECT * FROM EMP1 WHERE (EMP_ID IN (SELECT emp_id FROM 
emp1 GROUP BY emp_id  HAVING COUNT(emp_id) > 1))

--select * from #TempR
select * into #temp2 from #TempR

--  select * from #temp2

alter table #temp2 add record_id numeric(5,0) identity not 
null

/*        select those row which are repeated           */

  select * into #qwe
  from #temp2 
  where exists(
  
  select null from #temp2 b
  where b.ID = #temp2.ID
  and b.TYPE = #temp2.TYPE
  group by b.ID, b.TYPE
  having 
  count (*) >=2
  )

  --select * from #qwe

  


/*               delete those row which are repeted     */

delete from #TempR where ID in ( select ID from #qwe)

/*               insert those row which are deleted     */

delete from #qwe where record_id not in (  

    select record_id
  from #qwe
  group by ID, TYPE
  having record_id = max (record_id)
)

 -- select * from #qwe
alter table #qwe drop record_id

insert into #TempR
select * from #qwe 

/*             see output     */


select * from #TempR

/*         check for row getting repeted         */


select * 
  from #TempR 
  where exists(
  
  select null from #TempR b
  where b.ID = #TempR.ID
  and b.TYPE = #TempR.TYPE
  group by b.TT_ID, b.EQP_TYPE
  having 
  count (*) >=2
  )

begin 
select distinct * into #one  from four  where id in (select 
id from four group by id 
having count(*)>1)
delete from four where id in (select id from four group by 
id   having count(*)>1)
insert into four select * from #one
end

Tbale emp had some duplicate entries and i wanted to retain 
the first of all duplicates, the others could be deleted as 
follows . Please give your comments if this is the most 
optimum way :

delete from emp where rowid in 
(select rowid from emp o where rowid != 
 (select min(rowid) from emp i where i.empno=o.empno));

Select * from ( select rownum k,g.* from nbfc_bank_m g) h,
(select max(rownum) a,max(rownum)-1 b,max(rownum)-2 c 
	  from nbfc_bank_m ) p
where  h.k in (p.a,p.b,p.c)

select top 3 from <table name > where < specify any
condition> order by <column ID> desc

select * from emp
minus
select * from emp where rownum<(select count(*)-2 from emp)

select column_name from table_name 997,1000

1-select * from employee where emp_id >(select count(*)-3 
from employee)

2-select * from employee where emp_id in
(
select top 3 emp_id from employee order by emp_id DESC

)

hi  this is tulasi ravi
id - ravi106109@gmail.com

 select empno,sal from emp where rowid in
 (select rowid from emp
 minus
 select rowid from emp where rownum<=(
 select count(*)-3 from emp))


feel free to mail queries,,,.....

hi  this is tulasi ravi
id - ravi106109@gmail.com

 select * from emp where rowid in
 (select rowid from emp
 minus
 select rowid from emp where rownum<=(
 select count(*)-3 from emp))


feel free to mail queries,,,.....

select top 3 * from tablename 
order by 1 desc

By
Kumar Junior Software Engineer

drop table #temp select identity(int,1,1) as SlNo, * into 
#temp from TABLE_NAME select top 3 * from #temp order by  
SlNo desc

arun_4454@yahoo.co.in

very good kumar 
a sweet and simple query
i tink u can go to microsoft for work
wat a query it is 
good keep it up

You will be do following things one by one in sql server.

first create table.

1.create table tablename(sno int identity(1,1) primar key,
names varchar(100))

insert the record one by one.

2. 
Insert into tablename(names) values ('arun')
Insert into tablename(names) values ('arun')
Insert into tablename(names) values ('arun')
Insert into tablename(names) values ('arun')
.
.
.
.
To Insert 1000 record one by one.

Then to execute the following query to get last 3 record.

select top 3 * from tablename 
order by 1 desc


you will be get the correct answer ok.

By
kumar

select top 3 <column_name> from ,table_name> order by 
<column_name> desc

this will work.

Select * From Sample Where 
Srno In (Select Top 3 srno From Sample Order  By 1 Desc)

-- Sample is a Table Name and Srno is Unique Key column
-- This is the perfect Answer ...!

select * from table_name where  <Primary key Column-
name>   != all
(select  top (@@rowcount-3) <Primary key Column-name> from 
Table_name)

sql>Select rownum,rownid,level,empno,ename from emp
connect by prior empno=mgr where mgr is null;

o/p
Rownum   Rowid     Level      empno      ename
------   -----     -----      -----      -----
1       AABBBCCDA    1         5678      abcd
2       AABBBCCDB    1         9087      poiu
3       AABBBCCDC    2         8567      QWER
4       AABBBCCDD    2         7321      LKJH
5       AABBBCCDE    3         1284      ZXCV

For more details go through this link
http://www.psoug.org/reference/connectby.html

Connect by prior can be used to create heirarchical tree 
structure

Different Types Of JOINS:
     Most of the joins you will come across are based on 
equality, with the equijoin being the most dominant. 
    In this chapter you learned about equijoins; there are 
other types of joins you must become familiar with, most 
notably the self-join, the nonequijoin, and the outer join. 
  
Equijoin or Inner Join (Equality) : Traditional comma-
separated join or ANSI JOIN syntax (including optional 
INNER keyword). 
Natural Join (Equality) : NATURAL JOIN keyword. 
Cross-Join or Cartesian Product (No join condition): 
Traditional comma-separated with the missing join condition 
in the WHERE clause or CROSS JOIN keyword. 
Self-Join (Equality): Equijoin or Inner Join. 
Outer Join (left, right, full):(Equality and extending the 
result set): "Complex Joins" OUTER JOIN keywords or outer 
join operator(+). 
Non-Equijoin (Nonequality of values): "Complex Joins" 
Traditional comma-separated join or ANSI join syntax with 
the ON clause. 
The join criteria is not based on equality.

In SQL Server 2000 we have three types of joins
1. Inner join
2. Outer Join
3. Cross Join
1. Inner Join: Inner Join is the default type of join, it 
will producesses the result set, which contains matched 
rows only.
syntax: select * from table1<innerjoin>table2

2. Outer Join: Outer join produces the results, which 
contains matched rows and unmatched rows.
here we have three types of joins,
1.Left Outer Join 2.Right Outer Join 3.Full Outer Join
Left Outer Join: Left Outer Join producesses the results, 
which contains all the rows from Left table and matched 
rows from Right Table.
syntax: select * from table1<leftouterjoin>table2

Right Outer Join: Right Outer Join producesses the 
resultset, which contains all the rows from right table and 
matched rows from left table.
syntax:select * from table1<right outer join>table2

Full Outer Join: Full Outer Join producesses the resultset, 
which contains all the rows from left table and all the 
rows from right table.
syntax:select * from table1<fullouterjoin>table2

3.Cross Join: A join without having any condition is known 
as Cross Join, in cross join every row in first table is 
joins with every row in second table.
syntax: select * from table1<cross join>table2

Self Join: A join joins withitself is called self join
working with self joins we use Alias tables.

There are 1.equijoins 2.self joins,3.Crossjoins.
4.Outerjoins->Leftouterjoin,Rightouterjoin,Fullouterjoin.
1.EquiJoins:They are also called innerjoins,In this only 
matched rows are displayed to the user.
Example:
select eno,ename,sal,e.deptno,d.deptno,dname,loc from emp e 
innerjoin dept d on e.deptno=d.deptno
2.Selfjoin:if the table is joined with table itself.
select distinct (e1.empno),e1.ename,e1.sal from emp e1 join 
emp e2 on e1.sal=2* e2.sal(It dispalys emp deatails salary 
exactly 2 times with any other emp salary)

Already i explained 3 joins now i tell u Outerjoins.
leftouterjoin:It will display all the rows of left table 
irrespective of whether there is a match in the right or 
not.If there is no match in the right table then the null 
row is aasumed and it is displayed in the output.

Display all emps in the emptable.
select empno,ename,esal,e.deptno,d.deptno,dname,loc from 
emp e left outerjoin dept d on e.deptno=d.deptno

RightOuterJoin:It will display all the rows of right table 
irrespective of whether there is a match in the right or 
not.If there is no match in the Left table then the null 
row is aasumed and it is displayed in the output

Example
select empno,ename,esal,e.deptno,d.deptno,dname,loc from 
emp e right outerjoin dept d on e.deptno=d.deptno

There are six type of join in SQL 2000

1) INNER   JOIN
2) OUTER   JOIN
3) CROSS   JOIN
4) EQUI    JOIN
5) NATURAL JOIN
6) SELF    JOIN


1) INNER JOIN :- PRODUCESS THE RESULT SET OF MATCHING ROWS
                 ONLY FROM THE SPECIFIED TABLES.

EXAMPLE---

SELECT COLUMN_LIST FROM 1ST_TABLE_NAME JOIN 2ND_TABLE_NAME
ON 
1ST_TABLE_NAME.MATCING_COLUMN=2ND_TABLE_NAME.MATCING_COLUMN

2) OUTER JOIN :- DISPLAY ALL THE ROWS FROM THE FIRST TABLE
                 AND MATCHING ROWS FROM THE SECOND TABLE.

EXAMPLE---

SELECT COLUMN_LIST FROM 1ST_TABLE_NAME OUTER JOIN 
2ND_TABLE_NAME
ON 
1ST_TABLE_NAME.MATCING_COLUMN=2ND_TABLE_NAME.MATCING_COLUMN 
  
THERE ARE THREE TYPES OF OUTER JOIN:

A)LEFT  OUTER JOIN.
B)RIGHT OUTER JOIN.
C)FULL  OUTER JOIN

A)LFET OUTER JOIN :- DISPLAYS ALL THE ROWS FROM THE FIRST   
                     TABLE AND MATCHING ROWS FROM THE
                     SECOND TABLE.

EXAMPLE---

SELECT COLUMN_LIST FROM 1ST_TABLE_NAME LEFT OUTER JOIN 
2ND_TABLE_NAME      ON 
1ST_TABLE_NAME.MATCING_COLUMN=2ND_TABLE_NAME.MATCING_COLUMN

A)RIGHT OUTER JOIN :- DISPLAYS ALL THE ROWS FROM THE  
                      SECOND TABLE AND MATCHING ROWS FROM   
                      THE FIRST TABLE.

EXAMPLE---

SELECT COLUMN_LIST FROM 1ST_TABLE_NAME RIGHT OUTER JOIN 
2ND_TABLE_NAME        ON 
1ST_TABLE_NAME.MATCING_COLUMN=2ND_TABLE_NAME.MATCING_COLUMN

A)FULL OUTER JOIN :- DISPLAYS ALL MATCHING AND NONMATCHING
                     ROWS  OF BOTH THE TABLES.

EXAMPLE---

SELECT COLUMN_LIST  FROM 1ST_TABLE_NAME FULL OUTER JOIN 
2ND_TABLE_NAME     ON 
1ST_TABLE_NAME.MATCING_COLUMN=2ND_TABLE_NAME.MATCING_COLUMN

3)CROSS JOIN :- IN THIS TYPE OF JOIN, EACH ROWS FROM THE   
                JOIN WITH EACH ROWS FROM THE SECOND TABLE
                WITHOUT ANY CONDTION.
                ALSO CALLED AS CARTESIAN PRODUCT.

EXAMPLE---

SELECT COLUMN_LIST FROM 1ST_TABLE_NAME CROSS JOIN 
2ND_TABLE_NAME


4) EQUI JOIN :- DISPLAYS ALL THE MATHCING ROWS FROM JOINED  
                TABLE. AND ALSO DISPLAYS REDUNDANT VALUES.
                IN THIS WE USE * SIGN TO JOIN THE TABLE.

EXAMPLE---

SELECT * FROM 1ST_TABLE_NAME JOIN 2ND_TABLE_NAME
ON 
1ST_TABLE_NAME.MATCING_COLUMN=2ND_TABLE_NAME.MATCING_COLUMN 
 
5)NATURAL JOIN :- DISPLAYS ALL THE MATHCING ROWS FROM   
                    JOINED  TABLE.IT RESTRICT            
                    REDUNDANT VALUES.

6)SELF JOIN :- IN THIS TABLE JOIN WITH ITSELF WITH  
               DIFFERENT ALIAS NAME. 

ASSUME DEPARTMENT IS A TABLE:

SELECT A.DEP_NAME,B.MANAGER_ID(COLUMN LIST) FROM DEPARTMENT 
A JOIN 
DEPARTMENT B
ON A.MANAGER_ID=B.MANAGER_ID

http://en.wikipedia.org/wiki/Join_(SQL)

Indexes may be defined as unique or non-unique. A unique 
index acts as a constraint on the table by preventing 
identical rows in the index and thus, the original columns.

There can only be one clustered index but multiple non-
clustered index

One table can only one clustered index or primary key and
249 non-clustered index or unique key

In sqlserver we have two types of indexes (1) Clustered (2) 
non clustered indexes. 

Indexes may be simple index or composite index.

Index formed based on single column values is called simple 
index

index formed based on multiple column values is called 
composite index.

Default index is NON CLUSTERED INDEX 

When a table contains a primary key column then sqlserver 
automatically creates a unique clustered index.

Unique--- automatically creates a non clustered index 

so for this reason PRIMARY KEY IS NOT EQUAL TO UNIQUE + NOT 
NULL.

250 indexes(1 cluster index,249 non-cluster indexes)

four index keys is possible per one table.
read only table u havae create more then index per table

I think ,Default triggers in oracle are nothing but 
Constraints.Constraints are the triggers which are executed 
automatically at the time of DML operations.

Thanks
VD

i have answer regarding  on master and one slave running on
the status:-
                show slave status \G;

An inline command is nothing but and Inline view that act 
as a data-source for an Query.
Another important info about the Inline command is that it 
is not an Schema Object.

example :  (select rownum,e.eno 
      from (select eno from emp) e
       where rownum <=3)

The query will result the TOP_3_analysis .In which the query
(select eno from emp) is known as INLINE_view or 
INLINE_Query

Inline view is a subquery in the from clause of your main 
query.

Answers:

1)select avg(scost) from software where devin='ORACLE';

Hi,

 1.sql is structured quiery language      1.sql plus is an 
interfacing tool or medium through
                                                            
    sql statements will be passed to the oracle server.
2.Sql keywords cannot be abbreviated 2.sql plus cannot be 
abbreviated.

thanks
Shaik

The oracle engine will execute the command,and retrives all 
the data from the database and give it to the end user.

When we execute this command then the Oracle engine start 
the process and terieve the values and store them in 
IMPLICIT CURSOR then the values from this cursor are send 
to the user panel

Hi 

When we give this command select * from Emp; It will 
display emp table from oracle in database program.

It will retrieve all the columns from emp table.

When the above statment is fired, Oracle checks for 
following things,
1.Syntax Correctness.
2.User Priviledges on Emp Table.
If the above two things are correct then, all columns and 
all rows from Emp table are selected in an Implicit area 
and then are send to User.

Cheers
VD

Full table  scan  of emp  occurs and all the  records from 
emp is displayed

IT WILL GIVES THE ALL THE ATTRIBUTS IN EMP TABLE

hi 

we enter this query then oracle engine takes this query and
to search particular given table in database table then
select command act as a cursor to retrieve the data or to
fetched the
data from database table then to print the data on sql prompt

It will retrieve all the columns of emp table. There is no 
need to check for conditions as there are no conditions 
specified in the given command.

WE give this query when, we need to view the entire record 
of emp table. ( i.e) it will display all the rows and 
columns of emp table. and the oracle respond the same as 
per the query given.

Oracle search for this table in the database and returns 
all the rows.

when we fire select * from emp; first oracle engine checks 
the data dictionary called user_tables. if the table emp 
existed then it checks all permissions then contacts the 
data dictionary called user_tab_columns, and according to 
the given condition the sql statement will be executed

hI

I ama supporting Vaibhavi_dixit Answer.


Regards
Anand

Hi All,


When we'll excute any type of Query or SELECT statment in 
the SQL prompt. There are some steps proceed through which 
we'll get proper results. These steps are given belows

1. After write the Query
2. First it Parses according to their priority. (i.e Check 
the Correct syntax)
3. Binding the variables 
4. Fetch for proper data from proper table or not
5. Execute the whole query
6. Finally u'll got OUTPUT.

Regards\
Nageswar

Click the LOV icon in the object navigator and choose ur 
option of a manual or wizard .
A record group will be authomatically created depending on 
the LOV.

yes

Yes, you can run RAC (Real Application Clusters) that 
consist of multiple instances accessing the same database.  
You can also run the same and different versions of Oracle 
running on a single machine each of course accessing 
different databases.