Usefull Database Queries

1) How to get top  10 records from Mysql database?

select * from tablename  order by id asc limit 10;

2) How to get top  10 records from SQL sever database?

select top(10) * from tablename;

3) Insert multiple records  at a time in sqlserver table?

INSERT

  INTO destination

     ( col1, col2, col3 )

VALUES

     ( 'this', 'that', 'the other' )

   , ( 'something', 'nothing', 'anything' )

How to Delete Duplicate records from table based on rownumber

4)First create one table like this  and Insert values with duplicates

create table TestDuplicate(id int, name varchar(10),answer varchar(100));

insert into TestDuplicate values(4,'veera','Mtech')

with temptable as

(

select row_number()over (partition by id,name,answer order by name)as rownumber,* from TestDuplicate)

Delete from temptable where rownumber>1

select * from TestDuplicate

5)

create table sample( id int identity(1,1),patid int,name varchar(10))

insert into sample values(115,'raki')

select * from sample

/** select More than 1 (duplicate) record from table */

========================================================

SELECT patid,

COUNT(patid) AS NumOccurrences

FROM sample

GROUP BY patid

HAVING ( COUNT(patid) > 1 )

/* Delte Duplicate records from table */

==================================================

   Delete  FROM sample WHERE id NOT IN (SELECT MIN(id)

      FROM sample GROUP BY patid)

==============Copy records from one table to other==========

For Newtable :      Select * into newtbl from oldtbl

For Exicisting tbl: insert into oldtbl select * from  newtbl

===============Rename table===================

Sp_rename [oldtbl][newtbl];

Query to get records between 07/08/2012 and 10/08/2012

If you want get records between these two days we need to write a query like as shown below

SELECT * FROM #temp WHERE CONVERT(VARCHAR(10),CrDate,103) BETWEENCONVERT(VARCHAR(10),'07/08/2012',103) AND CONVERT(VARCHAR(10),'10/08/2012',103)

Reset Identity

=======================

DBCC CHECKIDENT ('UserDetails', RESEED, 0)

Get Employee Details with Particular Highest salary

SELECT * FROM EmployeeDetails e1 WHERE (n-1) = ( SELECT COUNT(DISTINCT(e2.Salary)) FROM EmployeeDetails e2 WHERE e2.Salary > e1.Salary)

Here in above query we need to replace the “n” value with required highest salary of table