Databases
StackOverflow2013
Postsdbo
PO
 

Note that there are some explanatory texts on larger screens.

plurals
  1. PO
    primarykey
    data
    text
    <p>The following answers all use <code>date_trunc('day',date_time)</code> or just cast to <code>date</code> to truncate a timestamp to a date. There's no need to jump through hoops with date formatting and strings. See <a href="http://www.postgresql.org/docs/current/static/functions-datetime.html" rel="nofollow noreferrer">Date/time functions</a> in the manual.</p> <p>This SQLFiddle shows three possible answers: <a href="http://sqlfiddle.com/#!12/0fd51/14" rel="nofollow noreferrer">http://sqlfiddle.com/#!12/0fd51/14</a>, all of which produce the same result for the input data (but not necessarily the same result if <code>date_time</code> can have duplicates in it).</p> <p>To solve your problem you could use a correlated subquery with a limit to generate an IN-list to filter on:</p> <pre><code>SELECT a.date_time, a.other_column FROM table1 a WHERE a.date_time IN ( SELECT b.date_time FROM table1 b WHERE b.date_time IS NOT NULL AND a.date_time::date = b.date_time::date ORDER BY b.date_time LIMIT 3 ) AND a.date_time::date BETWEEN '2012-11-02' AND '2012-11-05'; </code></pre> <p>This should be the most portable approach - though it won't work with MySQL (at least as of 5.5) because <a href="https://stackoverflow.com/a/13411307/398670">MySQL doesn't support <code>LIMIT</code> in a subquery used in an <code>IN</code> clause</a>. It works in SQLite3 and PostgreSQL, though, and should work in most other DBs.</p> <p>Another option would be to select the range of dates you wanted, annotate the rows within the range with a row number using a window function, then filter the output to exclude excess rows:</p> <pre><code>SELECT date_time, other_column FROM ( SELECT date_time, other_column, rank() OVER (PARTITION BY date_trunc('day',date_time) ORDER BY date_time) AS n FROM Table1 WHERE date_trunc('day',date_time) BETWEEN '2012-11-02' AND '2012-11-05' ORDER BY date_time ) numbered_rows WHERE n &lt; 4; </code></pre> <p>If ties are a possibility, ie if <code>date_time</code> is not unique, then consider using either the <code>rank</code> or <code>dense_rank</code> window functions instead of <code>row_number</code> to get deterministic results, or add an additional clause to the <code>ORDER BY</code> in <code>row_number</code> to break the tie.</p> <p>If you use <code>rank</code> then it'll include none of the rows if it can't fit all of them in; if you use <code>dense_rank</code> it'll include all of them even if it has to go over the 3-row-per-day limit to do so.</p> <p>All sorts of other processing are possible this way too, using the window specification.</p> <hr> <p>Here's yet another formulation that uses array aggregation and slicing, which is completely PostgreSQL specific but fun.</p> <pre><code>SELECT b.date_time, b.other_column FROM ( SELECT array_agg(a.date_time ORDER BY a.date_time) FROM table1 a WHERE a.date_time::date BETWEEN '2012-11-02' AND '2012-11-05' GROUP BY a.date_time::date ) x(arr) INNER JOIN table1 b ON (b.date_time = ANY (arr[1: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. USCraig Ringer
    plurals
    1. This table or related slice is empty.
    1. This table or related slice is empty.
    1. This table or related slice is empty.
    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. This table or related slice is empty.
 

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