HAVING id IN (SELECT TOP 10 PERCENT ... Question !!

[hapm]

Taking a second look, and now I'm sure thats what the query does. You won't need the grouping of course when the ID is unique, else it will eliminate duplicates. Using distinct would work as well. The current condition in the having clause should work the same in a where clause so.

[johnseito]

Thanks June and Hapm.

So why the self-join? Why the nested? What did developer want to learn from this? No idea. Makes no sense to me.

Sorry that is has taken me a couple of days to respond.

I just noticed something new regarding it. For column B, I noticed it is making sure all the unique column B are in the Query result, in addition
they are all 10%. I believe that is what the query is trying to do and is the intention. However, I don't know how that query achieved it with that code.

If I was looking at it, just analyzing the SQL statement, I don't see that is it's purpose. that I had looked at it deeper I do see it's purpose.


Some of the example of column B and how many of it's counts are taken are below:

Could you please help me shed some light as to how the SQL statement produced this result ?

Thank you so much !!!

0.6723439179761aehe	7
0.8466972252941aehe	7
0.2724605103696aehe	6
0.6232732871506aehe	6
0.0467529475825aehe	5
0.1873377808511aehe	5

[hapm]

In such complex queries I mostly try to solve it like a math expression, evaluating things in brackets from inside to outside. This is how the compiler does it as well normally. But if you look on that sub query there is a reference to a field of the main query. And that is a big change on how the query is executed. And it is the reason why you can't get on this be simply running the query on it's own without this references. Normally (without the references) the sub query would be executed once, as the set you are working on and the parameters you use to filter it, will be always the same, and therefore the result will be the same as well. But as there is a reference to the main query, the filter on the sub query changes on every record of the main query. So the sub query will be executed for every record in the main table.

To get some idea of what happens, it's best to take one record from the example table and use it's columns values as the filter values in the sub query. You will get a record set that is used only to filter this exact record from the main query. Hope that makes sense to you.

Using these forms of sub queries of course has some effect on the performance, as the number of sub queries evaluated raises with the number of record from the main query. But afaik there is no other way to get such results.

[johnseito]

sub query as the SELECT top 10 percent of ?

I don't see how that query can make sure every item in column B is selected, and 10% of it as well.

the query doesn't seem that complicated since is not that long, however, I don't see where is indication that is doing just that.

Just looking at it, I don't see which query statement is making sure all items in column B are in the result query.

[hapm]

Take it the other way around: what records are excluded from the result. These that are not under the first 10% of records with the same B column. So there are always 10% of the records with the same B column in the result. Would be interesting what happens with values of the B column where there are less then 10 records in your table.

[lfpm062010]

sub query as the SELECT top 10 percent of ?

I don't see how that query can make sure every item in column B is selected, and 10% of it as well.

the query doesn't seem that complicated since is not that long, however, I don't see where is indication that is doing just that.

Just looking at it, I don't see which query statement is making sure all items in column B are in the result query.

This is how the query used to generate your results.
SELECT tableEXAMPLE.ID, tableEXAMPLE.B, tableEXAMPLE.C, tableEXAMPLE.D, tableEXAMPLE.E
FROM tableEXAMPLE
GROUP BY tableEXAMPLE.ID, tableEXAMPLE.B, tableEXAMPLE.C, tableEXAMPLE.D, tableEXAMPLE.E
HAVING ((
(tableEXAMPLE.ID) IN (SELECT TOP 10 PERCENT ID FROM tableEXAMPLE as q
WHERE q.C = tableEXAMPLE.C and t.D = tableEXAMPLE.D ORDER BY id)));

The criteria HAVING and join the two tables using C and D will generate a different record set. Look at that record set closely. Hopefully that will give you some idea why the extra 64 records are generated.

[johnseito]

Ok so HAVING in a way means that, what
is in table tableEXAMPLE (column B, and Column C) is also now in the Query result ?

But how does it do that as 10% ? Does it go by the count of each item in Column B and result
10% of each item ? If this is true, then q.B = tableExample.B should work too without q.C = tableExample.b because is redundancy.

I tested it and just q.B = tableExample.B works, I still get 195.

[johnseito]

The query have to self join for it to have a % of each item hence that is why is not % of total line items from tableEXAMPLE but % of each item.
If is just SELECT % of tableEXAMPLE, it will just a % of what is pick
and that means it will not include all items in column B.

The question then becomes,

How do you do a query, not a self join query, that includes a % of every items from column B ?

[lfpm062010]

The Query-131 is picking the top 10% from the table (like it suppose to).

If you join the table with B and C data, you get 35612 rows.
The ID 8 did not get pick up because there are 69 records matched and top 10% will only give you 7.
The ID 1304 did get pick up because there is only one record matched and top 10% will always give you one record (not verified, but I think that is how top 10 works).

[johnseito]

If you join the table with B and C data, you get 35612 rows.

yes that is only if they are not doing a nested query. The result is huge, so that means we can't get every item in column B
and a % of it without a self join sub-query.

The ID 8 did not get pick up because there are 69 records matched and top 10% will only give you 7.
The ID 1304 did get pick up because there is only one record matched and top 10% will always give you one record (not verified, but I think that is how top 10 works).

I see you are correct about this !