sql select earliest date for multiple rows
greatest-n-per-group, postgresql, sql
Solution
select customer_name,
kwh,
reading_date,
reading_time
from (
select customer_name,
kwh,
reading_time,
reading_date,
row_number() over (partition by customer_name order by reading_time) as rn
from readings
where reading_date = date '2012-11-17'
) t
where rn = 1
As an alternative:
select r1.customer_name,
r1.kwh,
r1.reading_date,
r1.reading_time
from readings r1
where reading_date = date '2012-11-17'
and reading_time = (select min(r2.reading_time)
from readings
where r2.customer_name = r1.customer_name
and r2.read_date = r1.reading_date);
But I'd expect the first one to be faster.
Btw: why do you store date and time in two separate columns? Are you aware that this could be handled better with a `timestamp` column?
Problem
I have a database that looks like the following; ``` circuit_uid | customer_name | location | reading_date | reading_time | amps | volts | kw | kwh | kva | pf | key -------------------------------------------------------------------------------------------------------------------------------------- cu1.cb1.r1 | Customer 1 | 12.01.a1 | 2012-01-02 | 00:01:01 | 4.51 | 229.32 | 1.03 | 87 | 1.03 | 0.85 | 15 cu1.cb1.r1 | Customer 1 | 12.01.a1 | 2012-01-02 | 01:01:01 | 4.18 | 230.3 | 0.96 | 90 | 0.96 | 0.84 | 16 cu1.cb1.s2 | Customer 2 | 10.01.a1 | 2012-01-02 | 00:01:01 | 7.34 | 228.14 | 1.67 | 179 | 1.67 | 0.88 | 24009 cu1.cb1.s2 | Customer 2 | 10.01.a1 | 2012-01-02 | 01:01:01 | 9.07 | 228.4 | 2.07 | 182 | 2.07 | 0.85 | 24010 cu1.cb1.r1 | Customer 3 | 01.01.a1 | 2012-01-02 | 00:01:01 | 7.32 | 229.01 | 1.68 | 223 | 1.68 | 0.89 | 48003 cu1.cb1.r1 | Customer 3 | 01.01.a1 | 2012-01-02 | 01:01:01 | 6.61 | 228.29 | 1.51 | 226 | 1.51 | 0.88 | 48004 ``` What I am trying to do is produce a result that has the KWH reading for each customer from the earliest (`min(reading_time)`) on that date, the date will be selected by the user in a web form. The result would be/should be similar to; ``` Customer 1 87 Customer 2 179 Customer 3 223 ``` There are more than the number of rows per day shown here and there are more customers and the number of customers would change regularly. I do not have much experience with SQL, I have looked at subqueries etc. but I do not have the chops to figure out how arrange it by the earliest reading per customer and then just output the `kwh` column. This is running in PostgreSQL 8.4 on Redhat/CentOS.