Limit the number of rows in SQL query results

How to get only the first n rows from the subquery or child table's results?

Sometimes we need to fetch first n rows from the table(s). Different RDBMSs offer different functions to ease this task but unfortunately Sybase has no direct answer/solution.

BTW, If we need to get first 10 rows/records from a table

In Oracle 11g/12c

SELECT * FROM Inventory_tbl WHERE ROWNUM < 11

In IBM DB2, Oracle (12c), PostgreSQL (v8.3 and later)

SELECT * FROM Inventory_tbl FETCH FIRST 10 ROWS ONLY

In Microsoft SQL Server 2000+, Sybase ASE 12.5.x+

SELECT TOP 10 * FROM Inventory_tbl

or

set rowcount 10

SELECT * FROM Inventory_tbl

In MySQL, PostgreSQL

SELECT * FROM Inventory_tbl LIMIT 10

This is pretty simple.. But when we need to limit the number of rows of sub-query or secondary table, Sybase ASE has no support. 

For our understanding:

I need to write a Query to return only top 10 records from the child table.. You can take the Tables sysobjects and syscolumns to try this: I want All the user tables from sysobjects and only first 5 matching columns from syscolumns (Here we need to restrict 2nd table i.e. syscolumns rows to 5) I have implemented using cursors and # tables but I need a single Query. (in MySQL we have LIMIT option and ORACLE ROWNUM but set rowcount or top n of Sybase is not helpful for subQuery)
select so.id, so.uid, so.name, sc.colid, sc.name from sysobjects so, syscolumns sc where so.id=sc.id and so.id in (select top 10 id from syscolumns sc where sc.id=so.id) order by so.id

A query similar to above would satisfy our requirements but top n functionality was not supported in subqueries.

The same in MySQL is very simple, as limit function restricts the rowcount

select so.id, so.uid, so.name, sc.colid, sc.name from sysobjects so, syscolumns sc where so.id=sc.id and so.id in (select id from syscolumns sc where sc.id=so.id limit 10) order by so.id

If you have any ideas please comment or share with me @ sybase.professional@gmail.com
Thanks and Regards, Kasi Dogga