Extend SELECT FIRST nn clause with ability to obtain all records with equal RANK
--------------------------------------------------------------------------------
Key: CORE-4367
URL: http://tracker.firebirdsql.org/browse/CORE-4367
Project: Firebird Core
Issue Type: Improvement
Components: Engine
Reporter: Pavel Zotov
Suppose we have to select all rows from table with minimal value of some field
that is calculated via DENSE_RANK():
recreate table t( x int, y int );
insert into t values(1, 100);
insert into t values(2, 200);
insert into t values(3, 300);
insert into t values(4, 100);
insert into t values(5, 100);
insert into t values(6, 200);
insert into t values(7, 300);
insert into t values(8, 200);
Currently we can do it like this:
select * from ( select t.*, dense_rank()over( order by y ) dr from t ) where
dr =1;
The derived table: select t.*, dense_rank()over( order by y ) dr from t - will
be already sorted when it is to be handled by outer select (we can not put
OVER() function in WHERE clause).
There is useful clause in MS SQL for such case:
select TOP 1 WITH TIES * from t order by dense_rank()over( order by y );
This clause works just like select FIRST but allows to select all records with
the same value of ORDER BY. So, it will select ALL rows with
dense_rank()over( order by y ) = 1. No need in derived table ( ==> this is
mostly "syntax sugar").
Is it possible to implement similar feature in FB ?
--
This message is automatically generated by JIRA.
-
If you think it was sent incorrectly contact one of the administrators:
http://tracker.firebirdsql.org/secure/Administrators.jspa
-
For more information on JIRA, see: http://www.atlassian.com/software/jira
------------------------------------------------------------------------------
Learn Graph Databases - Download FREE O'Reilly Book
"Graph Databases" is the definitive new guide to graph databases and their
applications. Written by three acclaimed leaders in the field,
this first edition is now available. Download your free book today!
http://p.sf.net/sfu/13534_NeoTech
Firebird-Devel mailing list, web interface at
https://lists.sourceforge.net/lists/listinfo/firebird-devel
