title: group_concat and how to use row number in sqlite
body: <p>i have a data in a table, column id and column date</p>
  
  <pre><code>id  || datetime
  1   || 2013-05-24 19:23:16
  2   || 2013-05-28 19:24:20
  3   || 2013-05-28 19:25:05  
  4   || 2013-05-30 19:25:39  
  5   || 2013-05-30 19:26:05  
  </code></pre>
  
  <p>how query to show only one day with count?or one row with count. so the result is.                                                                                                  </p>
  
  <pre><code>    || datetime            || count
  1   || 2013-05-24 19:23:16 ||  1
  2   || 2013-05-28 19:24:20 ||  2
  3   || 2013-05-30 19:25:39 ||  2
  </code></pre>
  
  <p>i try in mysql and it works , there is my query.</p>
  
  <pre><code>SELECT id, GROUP_CONCAT( datetime ) AS date, COUNT( id ) AS count
  FROM dataPetak
  GROUP BY DATE( datetime )
  </code></pre>
  
  <p>but when i use sqlite the result is gives me all in one string</p>
  
  <pre><code>5 || 2013-05-24 19:23:16,2013-05-28 19:24:20,2013-05-28 19:25:05,2013-05-30 19:25:39,2013-05-30 19:26:05 || 5
  </code></pre>
  
  <p>how to fix that?</p>
  
  <p>help me</p>
  
  <p><strong>EDIT</strong></p>
  
  <p>when i use query </p>
  
  <pre><code>SELECT [datetime], [count] FROM (SELECT MAX([datetime]) 'datetime', COUN
  T(*) 'count' FROM dataPetak GROUP BY CAST ([datetime] AS DATE)) t;
  </code></pre>
  
  <p>the result is</p>
  
  <pre><code>       2013-05-24 19:23:16 ||  1
         2013-05-28 19:24:20 ||  2
         2013-05-30 19:25:39 ||  2
  </code></pre>
  
  <p>how to use row number in sqlite? </p>
  
  <p>so the result is </p>
  
  <pre><code>1   || 2013-05-24 19:23:16 ||  1
  2   || 2013-05-28 19:24:20 ||  2
  3   || 2013-05-30 19:25:39 ||  2
  </code></pre>
  
  <p>when i try this query ,</p>
  
  <pre><code>SELECT ROW_NUMBER () OVER (ORDER BY [datetime]) No, [datetime], [count] FROM (SELECT MAX([datetime]) 'datetime', COUN
  T(*) 'count' FROM dataPetak GROUP BY CAST ([datetime] AS DATE)) t;
  </code></pre>
  
  <p>show the error</p>
  
  <pre><code>Error: near "(": syntax error
  </code></pre>
  
  <p>how to fix that?</p>