How to Select More Than 1 Record Per Day

SQL: How to select multiple records per day, assuming that each day contain more than 1 value, MySQL

You can use variable, to get resultset rownumbers since mysql doesn't support ROWNUM as oracle.

Please try this :

select * 
from (
    select id,date(date_time) as date_time,other_columns,
    case when @date = date(date_time) then 
        @rn := @rn + 1 
    else 
        @rn := 1 
    end as x, -- row numbers for each date_time
    @date := date(date_time)
    from your_table
    inner join(select @date := '', @rn:=1) as tmp
    order by date(date_time) asc
) as tbl
where x <= 5 --5 records for each date_time

The query above will give you 5 records (if any) for each day.

How to select more than 1 record per day?

I want to select at most 3 records per day from a specific date range.

SELECT date_time, other_column
FROM  (
   SELECT *, row_number() OVER (PARTITION BY date_time::date) AS rn
   FROM   tbl
   WHERE  date_time >= '2012-11-01 0:0'
   AND    date_time <  '2012-12-01 0:0'
   ) x
WHERE  rn < 4;

Major points

Use the window function row_number(). rank() or dense_rank() would be wrong according to the question - more than 3 records might be selected with timestamp duplicates.
Since you do not define which rows you want per day, the correct answer is not to include an ORDER BY clause in the window function. Gives you an arbitrary selection, which matches the question.
I changed your WHERE clause from
```
WHERE  date_time >= '20121101 00:00:00'  
AND    date_time <= '20121130 23:59:59'
```
to
```
WHERE  date_time >=  '2012-11-01 0:0'  
AND    date_time <   '2012-12-01 0:0'
```
Your syntax would fail for corner cases like '20121130 23:59:59.123'.
What @Craig suggested:
```
date_time::date BETWEEN '2012-11-02' AND '2012-11-05'
```
.. 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.

Solution for PostgreSQL 8.3

Best solution: Upgrade to a more recent version, preferably to the current version 9.2.

Other solutions:

For only few days you could employ UNION ALL:

SELECT date_time, other_column
FROM   tbl t1
WHERE  date_time >= '2012-11-01 0:0'
AND    date_time <  '2012-11-02 0:0'
LIMIT  3
)
UNION ALL 
(
SELECT date_time, other_column
FROM   tbl t1
WHERE  date_time >= '2012-11-02 0:0'
AND    date_time <  '2012-11-03 0:0'
LIMIT  3
)
...

Parenthesis are not optional here.

For more days there are workarounds with generate_series() - something like I posted here (including a link to more).

I might have solved it with a plpgsql function back in the old days before we had window functions:

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 >= $1::timestamp
   AND    t.date_time <  ($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 <= $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';

Call:

SELECT * FROM f_foo('2012-11-01', '2012-11-05', 3);

SQL: How to select one record per day, assuming that each day contain more than 1 value MySQL

To get the first entry for every date you can do

select * from value_magnitudes
where id in 
(
    SELECT min(id)
    FROM value_magnitudes
    WHERE magnitude_id = 234
    and date(reading_date) >= '2013-04-01'
    group by date(reading_date)
)

SQL Server: I have multiple records per day and I want to return only the first of the day

You could use group by along with min to accomplish this.

Depending on how your data is structured if you are assigning a unique sequential number to each record created you could just return the lowest number created per day. Otherwise you would need to return the ID of the record with the earliest DATETIME value per day.

--Assumes sequential IDs
select
    min(Id)
from
    [YourTable]
group by
    --the conversion is used to stip the time value out of the date/time
    convert(date, [YourDateTime]

MySQL query that selects all users that have more than one entry per day

This is the solution

select x.user_id, count(x.num_days)
from
(
select USER_ID, COUNT(USER_ID) AS NUM_DAYS
from data1 
group by user_id, posting_date
having count(user_id) > 1
) x
group by 1

Working SQL Fiddle

(I used a varchar for date for simplicity but it should work fine with date too. You can check with your own database)

select rows in sql with latest date for each ID repeated multiple times

This question has been asked before. Please see this question.

Using the accepted answer and adapting it to your problem you get:

SELECT tt.*
FROM myTable tt
INNER JOIN
    (SELECT ID, MAX(Date) AS MaxDateTime
    FROM myTable
    GROUP BY ID) groupedtt 
ON tt.ID = groupedtt.ID 
AND tt.Date = groupedtt.MaxDateTime

SQL query for finding records where count 1

Use the HAVING clause and GROUP By the fields that make the row unique

The below will find

all users that have more than one payment per day with the same account number

SELECT 
 user_id ,
 COUNT(*) count
FROM 
 PAYMENT
GROUP BY
 account,
 user_id ,
 date
HAVING
COUNT(*) > 1

Update
If you want to only include those that have a distinct ZIP you can get a distinct set first and then perform you HAVING/GROUP BY

 SELECT 
    user_id,
    account_no , 
    date,
        COUNT(*)
 FROM
    (SELECT DISTINCT
            user_id,
            account_no , 
            zip, 
            date
         FROM
            payment 
    
        ) 
        payment
 GROUP BY
    
    user_id,
    account_no , 
    
    date
HAVING COUNT(*) > 1

Get top 1 row of each group

;WITH cte AS
(
   SELECT *,
         ROW_NUMBER() OVER (PARTITION BY DocumentID ORDER BY DateCreated DESC) AS rn
   FROM DocumentStatusLogs
)
SELECT *
FROM cte
WHERE rn = 1

If you expect 2 entries per day, then this will arbitrarily pick one. To get both entries for a day, use DENSE_RANK instead

As for normalised or not, it depends if you want to:

maintain status in 2 places
preserve status history
...

As it stands, you preserve status history. If you want latest status in the parent table too (which is denormalisation) you'd need a trigger to maintain "status" in the parent. or drop this status history table.