How to filter Final Tables and ITM MTTs

[sasieightynine]

I'm using this for filtering out FT hands in reports. " NOT ((tourney_results.val_finish > 9) OR ((tourney_results.val_finish between 7 AND 9) AND (tourney_hand_player_statistics.cnt_players < 7))) " This filters out every hand when hero finishing 10th+ and also when hero is finishing between 7th and 9th but have not knocked out on the final table.

It's not working the way I expected so I've tried to create a more detailed expression filter:

tourney_hand_player_statistics.id_hand in (
SELECT id_hand, SUM(amt_before)
FROM tourney_hand_player_statistics
WHERE id_player = id_player
GROUP BY id_hand
HAVING SUM(amt_before) = (tourney_summary.cnt_players * 1500))

Why is it not working? (Expression is not valid)

[kraada]

Unfortunately I don't think this method of counting up the total chips in the tournament is going to work because of the way the joins and grouping need to work. I'll see what I can work up for you.

[sasieightynine]

Unfortunately I don't think this method of counting up the total chips in the tournament is going to work because of the way the joins and grouping need to work. I'll see what I can work up for you.

Thanks in advance.

[sasieightynine]

Ok I feel like I'm getting closer:

SELECT tn.id_hand
FROM
(SELECT id_hand, thps.id_tourney, ts.cnt_players, SUM(amt_before)
FROM tourney_hand_player_statistics thps
JOIN tourney_summary ts
ON thps.id_tourney = ts.id_tourney
GROUP BY id_hand, thps.id_tourney, ts.cnt_players
HAVING SUM(amt_before) = ts.cnt_players * 1500
ORDER BY id_hand) tn

It gives me the correct hand id's when I run it from pgAdmin query tool. But when I paste it to PT4 as expression filter as:

tourney_hand_player_statistics.id_hand in (
SELECT tn.id_hand
FROM (SELECT id_hand, thps.id_tourney, ts.cnt_players, SUM(amt_before)
FROM tourney_hand_player_statistics thps
JOIN tourney_summary ts
ON thps.id_tourney = ts.id_tourney
GROUP BY id_hand, thps.id_tourney, ts.cnt_players
HAVING SUM(amt_before) = ts.cnt_players * 1500
ORDER BY id_hand) tn
)

I can save it but it gives me nothing at all when the query has finished. Any idea?

[BillGatesIII]

I've gazed at your SQL for a couple of minutes and don't understand what you're trying to achieve

Let's take one step back if you don't mind. Why is this filter not working for you?

If you just want final tables you could use a much simpler filter - the ITM part is what is complicated.

For final tables you could filter on MTT and then add this filter expression:

tourney_results.val_finish between 1 and 9

[sasieightynine]

I've gazed at your SQL for a couple of minutes and don't understand what you're trying to achieve

Let's take one step back if you don't mind. Why is this filter not working for you?

If you just want final tables you could use a much simpler filter - the ITM part is what is complicated.

For final tables you could filter on MTT and then add this filter expression:

tourney_results.val_finish between 1 and 9

It's not working because it gives me all the hands of the tournaments not just the ones that were played on the final table. So I thought I could try to filter for hands with all the chips of the tournament in play. I need hands specifically those were played on the final table. I know the topic title is a bit different but why create a whole new topic when this one is quite similar to what i want to achieve.

[BillGatesIII]

I still don't get it. If you finish between 1 and 9, you have to be at the final table. Assuming we are talking about nine handed MTTs.

So this filter should work.

filter on MTT (normal Filter Type)

tourney_results.val_finish between 1 and 9 (Expression Filter)

At least it does when I create a Hands report and put in those two filters.

So I'm curious why it doesn't work for you, before digging into something much more complicated

[sasieightynine]

I still don't get it. If you finish between 1 and 9, you have to be at the final table. Assuming we are talking about nine handed MTTs.

So this filter should work.

filter on MTT (normal Filter Type)

tourney_results.val_finish between 1 and 9 (Expression Filter)

At least it does when I create a Hands report and put in those two filters.

So I'm curious why it doesn't work for you, before digging into something much more complicated

Ok, lets make this simple. I have a test database which contains one 45-man 5-table SNG, which I have won, this SNG has 161 hands that I have been dealt in, and there are 78 hands that I have played at the final table. Now when I create a report with this filter "tourney_results.val_finish between 1 and 9" . The report still shows all the 161 hands in the report not just the 78 FT hands that I want to filter. The columns in the report are: Player, Hands, Tournaments, Chips Won, Chips All-in adj.

[sasieightynine]

When I run this from pgAdmin query tool:

SELECT id_hand, thps.id_tourney, ts.cnt_players, SUM(amt_before)
FROM tourney_hand_player_statistics thps
JOIN tourney_summary ts
ON thps.id_tourney = ts.id_tourney
GROUP BY id_hand, thps.id_tourney, ts.cnt_players
HAVING SUM(amt_before) = ts.cnt_players * 1500
ORDER BY id_hand) tn

It gives me exactly what I need, the last 78 hands that were played on the final table:

"id_hand";"id_tourney";"cnt_players";"sum"
84;1;45;67500.00
85;1;45;67500.00
................
................
161;1;45;67500.00

But it gives me nothing when I try to run it from PT4 with a subquery like posted a few posts above earlier.

[BillGatesIII]

There is a known bug in the PT4 Filter Expression parser which messes things up if there is a Group By in the subquery and some other filter is used, I guess therefore your report will not work.

And I finally got it why using val_finish doesn't work, my bad, sorry for that. It will of course return all hands of the tourneys you finished in the top 9 and not only the final table hands

The good thing is I got a SQL statement that will give you the final table hands of any tourney that starts with 1500 chips.

Code: Select all

tourney_hand_player_statistics.id_hand in (
  select id_hand
  from (   
    select distinct
      thps.id_tourney
     ,thps.id_hand
     ,sum(thps.amt_before) over (partition by thps.id_hand) = (ts.cnt_players * 1500) as ft
    from tourney_hand_player_statistics as thps
    join tourney_summary as ts on ts.id_tourney = thps.id_tourney) as mtt
  where mtt.ft)

The irritating thing is that I created a much more beautiful piece of SQL that should work for any starting stack but the parser doesn't like it