Databases
StackOverflow2013
Postsdbo
PO
 

Note that there are some explanatory texts on larger screens.

plurals
  1. PO
    primarykey
    data
    text
    <blockquote> <p>I want to select <strong>at most 3 records</strong> per day from a specific date range.</p> </blockquote> <pre><code>SELECT date_time, other_column FROM ( SELECT *, row_number() OVER (PARTITION BY date_time::date) AS rn FROM tbl WHERE date_time &gt;= '2012-11-01 0:0' AND date_time &lt; '2012-12-01 0:0' ) x WHERE rn &lt; 4; </code></pre> <h3>Major points</h3> <ul> <li><p>Use the window function <a href="http://www.postgresql.org/docs/current/interactive/functions-window.html" rel="nofollow noreferrer"><strong><code>row_number()</code></strong></a>. <code>rank()</code> or <code>dense_rank()</code> would be wrong according to the question - more than 3 records might be selected with timestamp duplicates.</p></li> <li><p>Since you do not define <em>which</em> rows you want per day, the correct answer is not to include an <code>ORDER BY</code> clause in the window function. Gives you an arbitrary selection, which matches the question.</p></li> <li><p>I changed your <code>WHERE</code> clause from </p> <pre><code>WHERE date_time &gt;= '20121101 00:00:00' AND date_time &lt;= '20121130 23:59:59' </code></pre> <p>to</p> <pre><code>WHERE date_time &gt;= '2012-11-01 0:0' AND date_time &lt; '2012-12-01 0:0' </code></pre> <p>Your syntax would fail for corner cases like <code>'20121130 23:59:59.123'</code>.</p> <p>What @Craig suggested:</p> <pre><code>date_time::date BETWEEN '2012-11-02' AND '2012-11-05' </code></pre> <p>.. would work correctly, but is an anti-pattern regarding performance. If you apply a cast or a function to your database column in the expression, plain indexes cannot be used.</p></li> </ul> <h3>Solution for PostgreSQL 8.3</h3> <p><em>Best solution</em>: <a href="http://www.postgresql.org/support/versioning/" rel="nofollow noreferrer">Upgrade to a more recent version, preferably to the current version 9.2.</a></p> <p><em>Other solutions</em>:</p> <p>For only <em>few days</em> you could employ <code>UNION ALL</code>:</p> <pre><code>SELECT date_time, other_column FROM tbl t1 WHERE date_time &gt;= '2012-11-01 0:0' AND date_time &lt; '2012-11-02 0:0' LIMIT 3 ) UNION ALL ( SELECT date_time, other_column FROM tbl t1 WHERE date_time &gt;= '2012-11-02 0:0' AND date_time &lt; '2012-11-03 0:0' LIMIT 3 ) ... </code></pre> <p>Parenthesis are not optional here.</p> <p>For <em>more days</em> there are workarounds with <strong><code>generate_series()</code></strong> - something like I posted <a href="https://stackoverflow.com/a/8892805/939860"><strong>here</strong> (including a link to more)</a>.</p> <p>I might have solved it with a <strong>plpgsql function</strong> back in the old days before we had window functions:</p> <pre><code>CREATE OR REPLACE FUNCTION x.f_foo (date, date, integer , OUT date_time timestamp, OUT other_column text) RETURNS SETOF record AS $BODY$ DECLARE _last_day date; -- remember last day _ct integer := 1; -- count BEGIN FOR date_time, other_column IN SELECT t.date_time, t.other_column FROM tbl t WHERE t.date_time &gt;= $1::timestamp AND t.date_time &lt; ($2 + 1)::timestamp ORDER BY t.date_time::date LOOP IF date_time::date = _last_day THEN _ct := _ct + 1; ELSE _ct := 1; END IF; IF _ct &lt;= $3 THEN RETURN NEXT; END IF; _last_day := date_time::date; END LOOP; END; $BODY$ LANGUAGE plpgsql STABLE STRICT; COMMENT ON FUNCTION f_foo(date3, date, integer) IS 'Return n rows per day $1 .. date_from (incl.) $2 .. date_to (incl.) $3 .. maximim rows per day'; </code></pre> <p>Call:</p> <pre><code>SELECT * FROM f_foo('2012-11-01', '2012-11-05', 3); </code></pre>
    singulars
    1. This table or related slice is empty.
    1. POHow to select more than 1 record per day?
      singulars
      1. PTQuestion
    1. PTAnswer
    1. USCommunity
    1. USErwin Brandstetter
    plurals
    1. This table or related slice is empty.
    1. This table or related slice is empty.
    1. POHow to select more than 1 record per day?
      singulars
      1. PTQuestion
    1. This table or related slice is empty.
    1. VO
      singulars
      1. PO
      1. This table or related slice is empty.
      1. VTUpMod
    2. VO
      singulars
      1. PO
      1. This table or related slice is empty.
      1. VTUpMod
    3. VO
      singulars
      1. PO
      1. This table or related slice is empty.
      1. VTUpMod
    1. COGood point re cast vs time ranges. I covered the issues with `rank` and `dense_rank`; I was just suggesting them as tiebreakers if the rather under-specified question turned out to need more deterministic row selection at the expense of always getting exactly 3 rows.
      singulars
      1. PO
      1. USCraig Ringer
    2. COI got an error while executing the query. ERROR: syntax error at or near "OVER" http://www.postgresql.org/docs/9.1/static/tutorial-window.html I have tried trimmed it down to just run SELECT *, row_number() OVER() AS rn FROM mytable WHERE date_time>= '2012-11-01 0:0' AND date_time< '2012-12-01 0:0' It still not working.
      singulars
      1. PO
      1. USDrogba
    3. CO@Drogba: Seems like you neglected to mention your outdated version of PostgreSQL. Window functions were introduced with **PostgreSQL 8.4**.
      singulars
      1. PO
      1. USErwin Brandstetter
 

Querying!

 
Guidance
A row detail

Detail views are divided into sections. All the information in the data section comes from columns in the selected row. The other sections display data from other, related rows.

Related data can be related in a to-one or a to-many fashion. Captions of data related in a to-many fashion link to a list view showing a filtered view of the table.

Try moving around until you find a non-empty to-many entry and click on the label to get to one. You can move back to the root by clicking on the database name in the header.

SQuiL has stopped working due to an internal error.

If you are curious you may find further information in the browser console, which is accessible through the devtools (F12).

Reload