How to get top " n " number of rows from table

Suppose you want top n values from table , so execute following query
with replacement of following item as mention below :

1 )  Replace your our tablename with table_name]

2 )  Replace your "n" with row numbers  (how many rows you want )

[

Note :  You can do multiply and division operations also

]

Query for Top rows :

SELECT

MainSQL.* FROM ( SELECT t.*,ROW_NUMBER() OVER ( ORDER BY 1 ) Row_Number,COUNT(1) OVER ( ORDER BY 1 RANGE BETWEEN UNBOUNDED PRECEDING AND UNBOUNDED FOLLOWING ) Row_Count
FROM (SELECT * FROM [table_name]) t ORDER BY 1 ) MainSQL WHERE Row_Number >= 1
AND Row_Number < ( 1 + 2 ) ORDER BY Row_Number;


Query for Bottom  rows :

SELECT MainSQL.* FROM ( SELECT t.*,ROW_NUMBER() OVER ( ORDER BY 1 ) Row_Number,
COUNT(1) OVER ( ORDER BY 1 RANGE BETWEEN UNBOUNDED PRECEDING AND UNBOUNDED FOLLOWING ) Row_Count
FROM (SELECT * FROM [table_name]) t ORDER BY 1 ) MainSQL WHERE Row_Number >= 1
AND Row_Number < ( 10 - 2 ) ORDER BY Row_Number;

WHAT IS - > " SELECT * FROM [TABLE_NAME] WHERE 1=1 " ??

What is

select * from xxx where 1=0 what does this mean?
also there is one
select * from yyy where 1=1

??
??

ANSWER ->


1. Select * from table where 1=0
return just header of the fields (attribute)
but with 0 rows

2. Select * from table where 1=1
return whole table entries
this is same as select * from table


Simple ans.

Logical ?

If the list of conditions is not known at compile time and is instead built at run time, you don't have to worry about whether you have one or more than one condition. You can generate them all like:

and <condition> 

and concatenate them all together. With the 1=1 at the start, the initial and has something to associate with.
I've never seen this used for any kind of injection protection, as you say it doesn't seem like it would help much. I have seen it used as an implementation convenience. The SQL query engine will end up ignoring the 1=1 so it should have no performance impact.


 

" DUAL " IN SQL SERVER / ORACLE

What is DUAL in Oracle?

Dual is a table that is created by Oracle together with data dictionary. It consists of exactly one column named “dummy”, and one record. The value of that record is X.

You can check the content of the DUAL table using the following syntax.

SELECT * FROM dual

It will return only one record with the value ‘X’.

What is the reason for following error in SQL Server?

Msg 208, Level 16, State 1, Line 1
Invalid object name ‘dual’.

The reason behind the error shown above is your attempt to SELECT values from DUAL table in SQL Server. This table does not exist in SQL Server. Continue reading for workaround.

What is the Equivalent of DUAL in SQL Server to get current datetime?

Oracle:

select sysdate from dual

SQL Server:

SELECT GETDATE()

What is the equivalent of DUAL in SQL Server?

None. There is no need of Dual table in SQL Server at all.

Oracle:

select ‘something’ from dual

SQL Server:

SELECT ‘something’

I have to have DUAL table in SQL Server, what is the workaround?

If you have transferred your code from Oracle and you do not want to remove DUAL yet, you can create a DUAL table yourself in the SQL Server and use it.

Here is a quick script to do the said procedure.

CREATE TABLE DUAL(DUMMY VARCHAR(1)
)GOINSERT INTO DUAL (DUMMY)VALUES ('X')GO

After creating the DUAL table above just like in Oracle, you can now use DUAL table in SQL Server.