1

Topic: How in request to restrict an amount of produced lines depending on value of a column?

FB3. The conditional request:

select t.value, t.edate
from t
order by 1, 2 desc

For everyone value there are tens/hundreds edate. It is necessary for everyone value to restrict an amount produced edate, for example, to ten. If records for specific value less than ten to produce how many is. Probably? Looked at window functions, first, fetch, anything suitable I do not find.  it would not be desirable. Execute block, for select... do would arrange.

2

Re: How in request to restrict an amount of produced lines depending on value of a column?

Somehow so: select from select group by having

3

Re: How in request to restrict an amount of produced lines depending on value of a column?

windows functions-> row number partition by t.value

KreatorXXI wrote:

for everyone value to restrict an amount produced edate, for example, to ten

The amount of that is is specific - lines in group value generally, or unique values edate (can be at you will only natively edate, but hundred lines with it)?

4

Re: How in request to restrict an amount of produced lines depending on value of a column?

For a basis look here: http://www.sql.ru/forum/1283351/zapros-s-gruppirovkoy
But there I took not ten lines, but only one

5

Re: How in request to restrict an amount of produced lines depending on value of a column?

Arioch;
Thanks for . That type:

select t.value, t.edate;
row_number () over (partition by t.value order by t.edate desc)
from t
order by 1, 2 desc

Then select from select or CTE.
By the way, in a manual on language on window functions similar there is no example with simultaneous usage partition by and order by. That me also unsettled. I feel that the decision somewhere here...

6

Re: How in request to restrict an amount of produced lines depending on value of a column?

KreatorXXI wrote:

By the way, in a manual on language on window functions similar there is no example with simultaneous usage partition by and order by.

... I Will add. It is but only in dock on 4.0, and not in an explicit form when it is told already about concrete windows

7

Re: How in request to restrict an amount of produced lines depending on value of a column?

KreatorXXI wrote:

Looked at window functions, first, fetch, anything suitable I do not find.

Means, badly looked. Lines to number in group trifling matter.

KreatorXXI wrote:

Execute block, for select... do would arrange.

Too it is elementary. Enclosed for select, perhaps, it will be optimal, as it is easier for interrupting, when "suffices".

8

Re: How in request to restrict an amount of produced lines depending on value of a column?

KreatorXXI;
By the way for understanding as window functions work better to read to dock on 4.0. In it it is told about one very important part of the window functions (window frame) which explicit job it is not implemented in 3.0.

9

Re: How in request to restrict an amount of produced lines depending on value of a column?

Arioch wrote:

for a basis look here: http://www.sql.ru/forum/1283351/zapros-s-gruppirovkoy

This topic generally to "paste" in the forum beginning because window functions in details and on useful to life an example:-D there were considered

10

Re: How in request to restrict an amount of produced lines depending on value of a column?

For a night the understanding that is not absolutely good nevertheless came. Result correct, but records all are viewed. Not to eat well. In current basis on time all is class. But in the future it will be necessary to reconsider. Except how to use lateral join on mind comes of nothing. No, still it is possible to try cycles for select with an explicit output from them. Neither heart nor soul to it does not lie.

11

Re: How in request to restrict an amount of produced lines depending on value of a column?

KreatorXXI;
Well so. It is necessary to understand that for operation of window functions buffering, and all sampling entirely is required, predicates on CTE will not be  in a window.

12

Re: How in request to restrict an amount of produced lines depending on value of a column?

WildSery wrote:

Enclosed for select, perhaps, it will be optimal, as it is easier for interrupting, when "suffices".

And here it not the fact. It strongly depends on indexes and value distribution. Sometimes full scan to make more cheaply, than at first grouping across the field of (an index can and not to be), then again sampling on a bunched field + to also sorting according to other field.

13

Re: How in request to restrict an amount of produced lines depending on value of a column?

Simonov Denis;
The filter on row_number basically it would be possible and  from top to down, in a window

14

Re: How in request to restrict an amount of produced lines depending on value of a column?

Simonov Denis;
Yes, I understand all, to window functions of claims was not present. Simply it would be desirable to have still plus abbreviation "". Idea such. To refuse window functions, and to make type such:

select t1.value, t3.edate
from t1
lateral join (
select first 10 t2.edate
from t2
where t2.t1_id = t1.t1_id
order by t2.edate desc
) t3 on true
where t1....

Or it is unreal invented?

15

Re: How in request to restrict an amount of produced lines depending on value of a column?

Arioch;
There it is a lot of specificity which is not considered by the optimizer.
row_number Demands result sorting, according to mind works only with sentence ORDER BY in a window. Sorting there is accessible only plan SORT, i.e. without navigation usage on an index (ORDER INDEX) because there can be sections set PARTITION BY, and can and not to be. As it is known sorting SORT demands full . In itself PARTITION BY the same demands sorting, and is besides supported only SORT. It is not assured that it is possible easily .
predicates on fields in PARTITION BY it can be made without serious consequences, but only if at us exactly one window or a little, but with identical PARTITION BY.
It is necessary to understand that window functions not panacea. In certain cases they considerably can facilitate life, but it is not necessary to push them in all slots. By and large they are useful in various reports, but to do with them live samplings quickly returning result does not follow.

16

Re: How in request to restrict an amount of produced lines depending on value of a column?

KreatorXXI;
If developers make progress in periods lateral join will be in 4.0. But besides not the fact that in this case it will be faster. The grouping of one figs demands the full scanning it be made NATURAL with sorting, or by means of ORDER INDEX. In the first case and so there will be a buffering, about the second can work precisely, but the complete sample can demand more disk readings (if sampling really big). And then to all to it the second request with the overhead projector will be fulfilled. It is necessary to measure in general. It can turn out more cheaply, and can and is more expensive. Unambiguously to state that I would not become better.

17

Re: How in request to restrict an amount of produced lines depending on value of a column?

Simonov Denis;
Let's wait, we look. Now the construction first like does not scan all. Naturally, in the presence of the correct index. Therefore it is possible to expect good high-speed performance of the request resulted by me.

18

Re: How in request to restrict an amount of produced lines depending on value of a column?

KreatorXXI wrote:

Now the construction first like does not scan all. Naturally, in the presence of the correct index.

It yes, but you do not consider an expense for request of top level in which there is a grouping, and then additional  on sampling of 10 values edate for everyone value
Make already experiment that faster.