This topic has been archived. It cannot be replied.
-
工作学习 / IT技术讨论 / ask a simple question: there are many records in an intermediate table generated by some queries, I only want top n recoreds, how to handle this case using only standard SQL?
Thanks
-ebusiness(每天进步多一些);
2004-7-27
(#1819427@0)
-
都忘得差不多了standard的不明白
在SQL Server里就是select top n，
oracle里是用rowid
-hugefox(长得象熊的狐狸);
2004-7-27
{69}
(#1819438@0)
-
in oracle, you can try:
select * from table_name where rownum<n order by column_name
-lau(lau);
2004-7-27
(#1819682@0)
-
是的，不同的db写法是不一样的，MSSQL里，select top N from table_name,Oracle 里, select column_name from table_name where rownum< N。看你用什么数据库了
-hard20(hard20);
2004-7-28
(#1820572@0)
-
thanks for your guys. I know many db vendors provide some tools for this case, but I want to know: if no tools available, how to write a SQL script to handle the case?
-ebusiness(每天进步多一些);
2004-7-28
(#1820579@0)
-
你的理解还是不对，我们说的不是DB tools，我们说的是SQL语句，各个数据库厂商对SQL语句都在SQL90的标准下有自己的扩展，上面提到的就是SQL下和Oracle下语句的良种写法
-hard20(hard20);
2004-7-28
(#1820600@0)
-
He was asking how to write the script using only standard sql without any vendor extends, which is vendor independent.
-guestagain(guest again);
2004-7-28
(#1820633@0)
-
In this case, the only thing he can do is writing the sql, then, if jdbc2.0, use scrollableResultset, else, use for/next to get the records he want.
-xanada(㊣流水);
2004-7-28
(#1820676@0)
-
not alway. there has some tricks.
-guestagain(guest again);
2004-7-28
(#1821211@0)
-
mmm...how?
-xanada(㊣流水);
2004-7-28
(#1821230@0)
-
case by case
-guestagain(guest again);
2004-7-28
(#1821241@0)
-
SELECT *
FROM my_table a
WHERE N >= (SELECT COUNT(DISTINCT maxcol)
FROM my_table b
WHERE b.maxcol >= a.maxcol)
ORDER BY maxcol DESC;rt
-stevensun2000(小胖子);
2004-7-28
{2}
(#1820671@0)
-
check out http://www.craigsmullins.com/dbu_0502.htm
-schen(糊涂阿福);
2004-7-28
(#1821183@0)