All Questions

Tagged with
Filter by
Sorted by
Tagged with
5
votes
3answers
235 views

A more intelligent ntile

When using the ntile() window function, the major issue is that it arbitrarily groups into roughly equal parts regardless of the actual value. For example with the following query: select id,...
0
votes
0answers
59 views

Rank users by different periods and overall

I'm trying to create a ranking system for a game which should have 2 different rankings. Below you will find the Model (or the result after querying the database) which should be presented to the ...
2
votes
1answer
5k views

Solving “Gaps and Islands” with row_number() and dense_rank()?

How does one solve the islands part of gaps-and-islands with dense_rank() and row_number(). I've seen this now a few times and I'm wondering if someone could explain it, Let's use something like this ...
5
votes
1answer
690 views

Understand why rank() over doesn't fit to don't select duplicates rows

I would like to understand why I have different results I have a table called active_transfert where I log image transfert user_id | image_id | created_at --------|----------|----------- 1 |1 ...
0
votes
1answer
592 views

Rank function creating duplicate partitions? (two rank 1's for the same key)

The below query is outputting two rows. Given that I am partitioning on ref_number and also have ref_number in the where clause, I believe I should only ever get a single row back. WITH rank_cte AS (...
7
votes
1answer
5k views

How to optimise window queries in postgres

I have the following table with approximately 175k records: Column | Type | Modifiers ----------------+-----------------------------+-----------------------...
22
votes
4answers
46k views

Limit results to the first 2 ranking rows

In SQL Server 2008, I am using RANK() OVER (PARTITION BY Col2 ORDER BY Col3 DESC) to return data set with RANK. But I have hundreds of records for each partition, so I will get values from rank 1, 2, ...