SQL - Select First 10 Rows Only

SQL - Select first 10 rows only?

In SQL server, use:

select top 10 ...

e.g.

select top 100 * from myTable
select top 100 colA, colB from myTable

In MySQL, use:

select ... order by num desc limit 10

How to skip the first n rows in sql query

Query: in sql-server

DECLARE @N INT = 5 --Any random number

SELECT * FROM (
        SELECT ROW_NUMBER() OVER(ORDER BY ID) AS RoNum
              , ID --Add any fields needed here (or replace ID by *)
        FROM TABLE_NAME
) AS tbl 
WHERE @N < RoNum
ORDER BY tbl.ID

This will give rows of Table, where rownumber is starting from @N + 1.

How to query first 10 rows and next time query other 10 rows from table

Just use the LIMIT clause.

SELECT * FROM `msgtable` WHERE `cdate`='18/07/2012' LIMIT 10

And from the next call you can do this way:

SELECT * FROM `msgtable` WHERE `cdate`='18/07/2012' LIMIT 10 OFFSET 10

More information on OFFSET and LIMIT on LIMIT and OFFSET.

how to select first N rows from a table in T-SQL?

select top(@count) * from users

If @count is a constant, you can drop the parentheses:

select top 42 * from users

(the latter works on SQL Server 2000 too, while the former requires at least 2005)

Do I have to use FETCH FIRST n ROWS ONLY or LIMIT when selecting an unique id in where clause

You can use both the FETCH FIRST 1 ROWS ONLY as well as LIMIT in Db2, check the DB2 compatibility settings.

If only one row is returned, it does not matter if that syntax is specified. However, if you or the system is not sure, then it is an extra safeguard. Depending on the optimizer settings (or mood or statistics or metadata), the additional syntax may help with performance. The reason is that the database system knows that only 1 row should be returned and it can optimize for that case.

If there is a unique index on id, then it should be obvious, but is there an index...?

SELECT TOP 10 rows

You need to calculate the top Customers first, then pull out all their products. You can do this with a Common Table Expression.

As you haven't provided any test data this is untested, but I think it will work for you:

with top10 as
(
    select top 10 CUSTOMER_ID
                 ,sum(S90T01_GROSS_EXPOSURE_THSD_EUR) as TOTAL_S90T01_GROSS_EXPOSURE_THSD_EUR
    from [dbo].[DM_07MONTHLY_DATA]
    where S90T01_CLIENT_SEGMENT = 'PI'
          and YYYY_MM = '2017_01'
    group by CUSTOMER_ID
    order by TOTAL_S90T01_GROSS_EXPOSURE_THSD_EUR desc
)
select m.CUSTOMER_ID
        ,m.S90T01_GROSS_EXPOSURE_THSD_EUR
        ,m.S90T01_COGNOS_PROD_NAME
        ,m.S90T01_DPD_C
        ,m.PREVIOUS_BUCKET_DPD_REP
        ,m.S90T01_BUCKET_DPD_REP
from [dbo].[DM_07MONTHLY_DATA] m
    join top10 t
        on m.CUSTOMER_ID = t.CUSTOMER_ID
order by t.TOTAL_S90T01_GROSS_EXPOSURE_THSD_EUR desc
    ,m.S90T01_GROSS_EXPOSURE_THSD_EUR;

Database agnostic query to select top 1 or top n rows?

Per the thread you linked to, there isn't much of a database agnostic solution:

• DB2 -- select * from table order by col fetch first 10 rows only
• Informix -- select first 10 * from table order by col
• Microsoft SQL Server and Access -- select top 10 * from table order by col
• MySQL and PostgreSQL -- select * from table order by col limit 10
• Oracle 8i -- select * from (select * from table order by col) where rownum <= 10

http://forums.mysql.com/read.php?60,4515,4678

Why fetch first 10 rows and select all rows cost same?

In simple terms, all of this is performed:

select this_.RIGHTSID as y0_
     , this_.RIGHTSNUM as y1_
     , rightstype1_.NAME as y2_
     , rightsstat3_.NAME as y3_
     , this_.ISSUEDATE as y4_
     , this_.EXPIRYDATE as y5_
     , licensee4_.NAME as y6_
     , agency2_.NAME as y7_
     , agency2_.AGENCYID as y8_
     , this_.EFFECTIVEDATE as y9_
     , rightstype1_.RIGHTSTYPEID as y10_ 
FROM RIGHTS_TB this_ 
    inner join RIGHTS_TYPE_TB rightstype1_ on this_.RIGHTSTYPEID=rightstype1_.RIGHTSTYPEID
    inner join AGENCY_TB agency2_ on rightstype1_.AGENCYID=agency2_.AGENCYID 
    inner join RIGHTS_STATUS_TB rightsstat3_ on this_.RIGHTSSTATUSID=rightsstat3_.RIGHTSSTATUSID 
    inner join LICENSEE_TB licensee4_ on this_.LICENSEEID=licensee4_.LICENSEEID 
WHERE this_.ACTIVE_IND='Y' 
ORDER BY this_.ISSUEDATE desc 

before this can be performed:

fetch first 10 rows only

It isn't possible to know which records are allowed until the full FROM and WHERE clauses are completed, then you cannot know which fit into the FIRST n until the ORDER is completed too

So it isn't at all surprising that the execution time is similar.

---

Related Topics