how to get nth hightst salary/student query

Here we will present one possible answer to finding the nth highest salary first, and the explanation of that answer after since it’s actually easier to understand that way. Note that the first answer we present is actually not optimal from a performance standpoint since it uses a subquery, but we think that it will be interesting for you to learn about because you might just learn something new about SQL. If you want to see the more optimal solutions first, you can skip down to the sections that says “Find the nth highest salary without a subquery” instead.
The SQL below will give you the correct answer – but you will have to plug in an actual value for N of course. This SQL to find the Nth highest salary should work in SQL Server, MySQL, DB2, Oracle, Teradata, and almost any other RDBMS:

SELECT * /*This is the outer query part */
FROM Employee Emp1
WHERE (N-1) = ( /* Subquery starts here */
SELECT COUNT(DISTINCT(Emp2.Salary))
FROM Employee Emp2
WHERE Emp2.Salary > Emp1.Salary)

DECLARE @tbl table(sid int,mark int)
insert into @tbl values(1,234)
insert into @tbl values(2,134)
insert into @tbl values(3,334)
insert into @tbl values(1,534)
insert into @tbl values(2,634)
insert into @tbl values(3,634)
insert into @tbl values(10,834) -- ??

SELECT mark,max(mark) FROM @tbl group by mark order by mark desc--having max(mark)

SELECT * FROM @tbl WHERE mark=(SELECT top 1 mark FROM @tbl WHERE mark<>(SELECT top 1 mark FROM @tbl order by mark desc) ORDER BY mark desc)

SELECT * /*This is the outer query part */
FROM @tbl s1
WHERE (3-1) = ( /* Subquery starts here */
SELECT COUNT(DISTINCT(s2.mark))
FROM @tbl s2
WHERE s2.mark > s1.mark)

DELETE Duplicate Records Using CTE

Syntax
With cte_duplicate (name, age, salary, rownumber)
as (
select name,age,salary, row_number()over(partition by name, age , salary order by name, age , salary)as rank from tbl_dublicatedata
)
delete from cte_duplicate where rownumber<>1  

Example :

BEGIN
   --Declare Statemant
    DECLARE @tblduplicatedata TABLE(name VARCHAR(50),age INT,salary INT);

    --Insert Statements
    INSERT INTO @tblduplicatedata(name,age,salary)VALUES('Sayali',12,45000);
    INSERT INTO @tblduplicatedata(name,age,salary)VALUES('Geet',15,85000);
    INSERT INTO @tblduplicatedata(name,age,salary)VALUES('Sayali',12,45000);
    
    WITH cte_duplicate (name, age, salary,rownumber)
        AS (
        SELECT name,age,salary, row_number()OVER(PARTITION BY name, age , salary ORDER BY name, age , salary)AS rank FROM @tblduplicatedata
        )
        DELETE FROM cte_duplicate WHERE rownumber<>1  

        SELECT * FROM @tblduplicatedata
END
 

how to know collation of a/all tables

List all table and collation name list 
 
SELECT col.name, col.collation_name,object_NAME(object_id),*
FROM sys.columns col WHERE collation_name='SQL_Latin1_General_CP1_CI_AS'


SELECT col.name, col.collation_name,object_NAME(object_id),*
FROM sys.columns col WHERE collation_name='Latin1_General_CI_AI'

Get collation name of a particular table
SELECT col.name, col.collation_name,object_NAME(object_id),*
FROM sys.columns col WHERE  object_id = OBJECT_ID('table_name')

How to get all table names in a database in sql server

SELECT db_name(), name,* FROM sys.tables;