New issue
Have a question about this project? Sign up for a free GitHub account to open an issue and contact its maintainers and the community.
By clicking “Sign up for GitHub”, you agree to our terms of service and privacy statement. We’ll occasionally send you account related emails.
Already on GitHub? Sign in to your account
New additional functions similar LIST [CORE2134] #2565
Comments
Modified by: Igor Lobov (ivl)Component: Engine [ 10000 ] |
Commented by: Bjoern Reimer (bnreimer) Wouldn't MAX and MIN do the job? |
Commented by: Igor Lobov (ivl) No. I think MAX and MIN is not applicable in lots cases. For example: Table
SELECT FIELD2, COUNT(FIELD2), MIN(FIELD3), FIRST(FIELD3) In this SELECT the functions MIN and FIRST will return different value. The result of MIN will be "1", but the result of FIRST will be "2" |
Commented by: @asfernandes Your desire will be achievable when full support of window functions is present. Something like: |
Commented by: Igor Lobov (ivl) Yes, this good way is fit for me. But ORACLE SQL that includes window functions contains FIRST_VALUE() and LAST_VALUE(), which return the first and last values in a window. |
Commented by: Smirnoff Serg (wildsery) select field2, cast(substring(min(lpad(field1, 10, '0') || field3) from 11) as int) |
Commented by: Igor Lobov (ivl) May be But if I have more than one group field I obtain very long expression. |
Commented by: Smirnoff Serg (wildsery) 1. There's no need to cast int as varchar. Moreover, no need external cast. select t1.field2, (select first 1 field3 from Table where field2 = t1.field2 order by field1) execute block returns (field2 int, field3 int) as |
Commented by: Igor Lobov (ivl) I think that a lot of different solutions exist in the world. I created the simple database with a single table I inserted 1000000 records into the table. Query 1 SELECT FIELD2, MIN(FIELD3) FROM TEST_TABLE Execute time = 2s 797ms Query 2 SELECT FIELD2, LIST(FIELD3) FROM TEST_TABLE Query 3 Execute time = 3s 359ms SELECT FIELD2, CAST(SUBSTRING(MIN(LPAD(FIELD1, 10, '0') || FIELD3) FROM 11) AS INT) FROM TEST_TABLE GROUP BY 1 Execute time = 3s 422ms Query 4 SELECT T1.FIELD2, (SELECT FIRST 1 FIELD3 FROM TEST_TABLE WHERE FIELD2 = T1.FIELD2 ORDER BY FIELD1) FROM TEST_TABLE T1 Execute time = 4m 8s 890ms Query 5 EXECUTE BLOCK RETURNS (FIELD2 INT, FIELD3 INT) AS Execute time = 3s 500ms First and second query were written as a sample of performance. I think that built-in function could be the universal and high-speed solution. Firebird 2.1.1 for Linux AMD64 under Fedora 9 x86_64 was used for my experiment. |
Commented by: Smirnoff Serg (wildsery) > The performance of Query 4 is bad very much. CREATE INDEX IDX1 ON TEST_TABLE (FIELD2, FIELD1); |
Commented by: Igor Lobov (ivl) You can not add index to every field. |
We have |
@dyemanov I think it can. |
Submitted by: Igor Lobov (ivl)
Votes: 2
Create additional functions that are similar LIST, but returns only FIRST, LAST or ANY value from the list.
The text was updated successfully, but these errors were encountered: