CODEWITHSUNDEEP
sqlmysqlsql-serverdatabaseoracle-database
jasmeet
jasmeet

Reputation: 11

how to select n rows

i have a database about several companies which include their name, price of share, %age change:

NAME           PRICE              % CHANGE
-------------------------------------------------
a              67                  11
b              23                  6
c              456                 5.89  
d              112                 23.98       
e              31                  17            
f              78                  3.09              
g              2678                12.56                   
h              357                 4.6

i want to select top 5 rows %age wise,means which hav maximum %age to be displayed at the top...n preceeding by jst 4 more value in desc. order...

OUTPUT SHOULD BE

NAME           PRICE              % CHANGE
-------------------------------------------------
d              112                 23.98   
e              31                  17            
g              2678                12.56  
a              67                  11   
b              23                  6

Upvotes: 1

Views: 557

Answers (5)

OMG Ponies
OMG Ponies

Reputation: 332561

Using MySQL/Postgres:

  SELECT t.name,
         t.price,
         t.change
    FROM TABLE t
ORDER BY t.change DESC
   LIMIT 5

LIMIT clause:

Using SQL Server:

  SELECT TOP 5
         t.name,
         t.price,
         t.change
    FROM TABLE t
ORDER BY t.change DESC

TOP is supported on SQL Server 2000+ at least

Oracle:

SELECT x.*
  FROM (SELECT t.name,
               t.price,
               t.change
          FROM TABLE t
      ORDER BY t.change DESC) x
 WHERE ROWNUM <= 5

Oracle's ROWNUM

Upvotes: 8

priyanka.sarkar
priyanka.sarkar

Reputation: 26498

select top 5 * from tblname order by change desc

Upvotes: 0

Cryophallion
Cryophallion

Reputation: 704

Order by and limit
SELECT * FROM company ORDER BY change DESC LIMIT 5;

Upvotes: 0

Franz
Franz

Reputation: 11553

How about this?

SELECT * FROM table ORDER BY change DESC LIMIT 5

EDIT: Note that changeshould be the name of the column that you listed as % CHANGE...

Upvotes: 0

intgr
intgr

Reputation: 20466

Unless I am misunderstanding something... This is basic SQL:

SELECT * FROM table ORDER BY change DESC LIMIT 5;

Upvotes: 1

Related Questions