Pure SQL Things have changed since 2008. You can use a window function to get the full count and the limited result in one query. Introduced with PostgreSQL 8.4 in 2009. SELECT foo , count(*) OVER() AS full_count FROM bar WHERE <some condition> ORDER BY <some col> LIMIT <pagesize> OFFSET <offset>; Note that this can … Read more
There is no need to worry about specifying constant in the ORDER BY expression. The following is quoted from the Microsoft SQL Server 2012 High-Performance T-SQL Using Window Functions written by Itzik Ben-Gan (it was available for free download from Microsoft free e-books site): As mentioned, a window order clause is mandatory, and SQL Server … Read more
Use: SELECT m.yr, COUNT(*) AS num_movies FROM MOVIE m JOIN CASTING c ON c.movieid = m.id JOIN ACTOR a ON a.id = c.actorid AND a.name=”John Travolta” GROUP BY m.yr ORDER BY num_movies DESC, m.yr DESC Ordering by num_movies DESC will put the highest values at the top of the resultset. If numerous years have the … Read more
Postgres 9.4 or later Use WITH ORDINALITY for set-returning functions: When a function in the FROM clause is suffixed by WITH ORDINALITY, a bigint column is appended to the output which starts from 1 and increments by 1 for each row of the function’s output. This is most useful in the case of set returning … Read more
SELECT tab.*, row_number() OVER () as rnum FROM tab; Here’s the relevant section in the docs. P.S. This, in fact, fully matches the answer in the referenced question.
Basically, you need a window function. That’s a standard feature nowadays. In addition to genuine window functions, you can use any aggregate function as window function in Postgres by appending an OVER clause. The special difficulty here is to get partitions and sort order right: SELECT ea_month, id, amount, ea_year, circle_id , sum(amount) OVER (PARTITION … Read more
RANK() gives you the ranking within your ordered partition. Ties are assigned the same rank, with the next ranking(s) skipped. So, if you have 3 items at rank 2, the next rank listed would be ranked 5. DENSE_RANK() again gives you the ranking within your ordered partition, but the ranks are consecutive. No ranks are … Read more
Did you try df.groupby(‘id’).head(2) Output generated: id value id 1 0 1 1 1 1 2 2 3 2 1 4 2 2 3 7 3 1 4 8 4 1 (Keep in mind that you might need to order/sort before, depending on your data) EDIT: As mentioned by the questioner, use df.groupby(‘id’).head(2).reset_index(drop=True) to remove … Read more
The PARTITION BY clause sets the range of records that will be used for each “GROUP” within the OVER clause. In your example SQL, DEPT_COUNT will return the number of employees within that department for every employee record. (It is as if you’re de-nomalising the emp table; you still return every record in the emp … Read more
The PARTITION BY clause sets the range of records that will be used for each “GROUP” within the OVER clause. In your example SQL, DEPT_COUNT will return the number of employees within that department for every employee record. (It is as if you’re de-nomalising the emp table; you still return every record in the emp … Read more