Re: [SQL] Query how-to
Hi,
What about something like that ?
select adate, sum(openedCount) as openedCount, sum(closedCount) as closedCount
from
(
select sum(case when status ='Closed' then stop_date else start_date end) as
adate,
sum(case when status ='Closed' then 1 else 0 end) as closedCount
sum(case when status ='New' then 1 else 0 end) as openedCount
from Yourtable
where status in ('Closed','New')
)x
group by adate
order by adate
HTH,
Marc
[SQL] For each key, find row with highest value of other field
I have some data of the form: Key | Date | Value A | 2008-05-01 | foo* A | 2008-04-01 | bar A | 2008-03-01 | foo* B | 2008-03-04 | baz B | 2008-02-04 | bar C | 2008-06-03 | foo* C | 2008-04-04 | baz C | 2008-03-04 | bar Is there any way to select only the rows marked with a (*) out of these without doing a join? I.e. I wish to find the row with the highest Date for each Key and use the Value from that. Regards, -- Raju -- Raj Mathur[EMAIL PROTECTED] http://kandalaya.org/ GPG: 78D4 FC67 367F 40E2 0DD5 0FEF C968 D0EF CC68 D17F PsyTrance & Chill: http://schizoid.in/ || It is the mind that moves -- Sent via pgsql-sql mailing list ([email protected]) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-sql
Re: [SQL] For each key, find row with highest value of other field
Howdy, Raju Do you want somethin like this? Not sure if I fully understood your requests... SELECT "Key",MAX(Date) FROM t_yourTable WHERE Value LIKE 'foo' GROUP BY Key ; Best, Oliveiros - Original Message - From: "Raj Mathur" <[EMAIL PROTECTED]> To: Sent: Friday, October 03, 2008 7:55 PM Subject: [SQL] For each key, find row with highest value of other field I have some data of the form: Key | Date | Value A | 2008-05-01 | foo* A | 2008-04-01 | bar A | 2008-03-01 | foo* B | 2008-03-04 | baz B | 2008-02-04 | bar C | 2008-06-03 | foo* C | 2008-04-04 | baz C | 2008-03-04 | bar Is there any way to select only the rows marked with a (*) out of these without doing a join? I.e. I wish to find the row with the highest Date for each Key and use the Value from that. Regards, -- Raju -- Raj Mathur[EMAIL PROTECTED] http://kandalaya.org/ GPG: 78D4 FC67 367F 40E2 0DD5 0FEF C968 D0EF CC68 D17F PsyTrance & Chill: http://schizoid.in/ || It is the mind that moves -- Sent via pgsql-sql mailing list ([email protected]) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-sql -- Sent via pgsql-sql mailing list ([email protected]) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-sql
Re: [SQL] For each key, find row with highest value of other field
> Raj Mathur wrote: > > I have some data of the form: > > Key | Date | Value > A | 2008-05-01 | foo* > A | 2008-04-01 | bar > A | 2008-03-01 | foo* > B | 2008-03-04 | baz > B | 2008-02-04 | bar > C | 2008-06-03 | foo* > C | 2008-04-04 | baz > C | 2008-03-04 | bar > > Is there any way to select only the rows marked with a (*) > out of these without doing a join? I.e. I wish to find the > row with the highest Date for each Key and use the Value from that. > This should do it: Select value from table a where date = (select max(b.date) from table b where b.key = a.key) q; Regards, Fernando. -- Sent via pgsql-sql mailing list ([email protected]) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-sql
Re: [SQL] For each key, find row with highest value of other field
select distinct on (Key) Key, Date, Value
from <>
order by Key, Date desc
MYDATABASE=> create table aaa (key varchar(1), date date, value text);
CREATE TABLE
Time: 1518.002 ms
MYDATABASE=> insert into aaa (key, date, value) values ('A',
'2008-05-01', 'foo');
INSERT 0 1
Time: 1.125 ms
MYDATABASE=> insert into aaa (key, date, value) values ('A',
'2008-04-01', 'bar');
INSERT 0 1
Time: 0.290 ms
MYDATABASE=> insert into aaa (key, date, value) values ('A',
'2008-03-01', 'foo');
INSERT 0 1
Time: 0.310 ms
MYDATABASE=> insert into aaa (key, date, value) values ('B',
'2008-03-01', 'baz');
INSERT 0 1
Time: 0.304 ms
MYDATABASE=> insert into aaa (key, date, value) values ('B',
'2008-02-01', 'bar');
INSERT 0 1
Time: 0.330 ms
MYDATABASE=> insert into aaa (key, date, value) values ('C',
'2008-06-03', 'foo');
INSERT 0 1
Time: 0.298 ms
MYDATABASE=> insert into aaa (key, date, value) values ('C',
'2008-04-04', 'baz');
INSERT 0 1
Time: 0.295 ms
MYDATABASE=> insert into aaa (key, date, value) values ('C',
'2008-03-04', 'bar');
INSERT 0 1
Time: 0.319 ms
MYDATABASE=> commit;
COMMIT
Time: 569.591 ms
MYDATABASE=> select * from aaa;
key |date| value
-++---
A | 2008-05-01 | foo
A | 2008-04-01 | bar
A | 2008-03-01 | foo
B | 2008-03-01 | baz
B | 2008-02-01 | bar
C | 2008-06-03 | foo
C | 2008-04-04 | baz
C | 2008-03-04 | bar
(8 rows)
Time: 0.520 ms
MYDATABASE=> select distinct on (key) key, date, value from aaa order by
key, date desc;
key |date| value
-++---
A | 2008-05-01 | foo
B | 2008-03-01 | baz
C | 2008-06-03 | foo
(3 rows)
Time: 0.524 ms
-Mark
On Sat, 2008-10-04 at 00:25 +0530, Raj Mathur wrote:
> I have some data of the form:
>
> Key | Date | Value
> A | 2008-05-01 | foo*
> A | 2008-04-01 | bar
> A | 2008-03-01 | foo*
> B | 2008-03-04 | baz
> B | 2008-02-04 | bar
> C | 2008-06-03 | foo*
> C | 2008-04-04 | baz
> C | 2008-03-04 | bar
>
> Is there any way to select only the rows marked with a (*) out of
> these
> without doing a join? I.e. I wish to find the row with the highest
> Date for each Key and use the Value from that.
>
> Regards,
>
--
Sent via pgsql-sql mailing list ([email protected])
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-sql
Re: [SQL] Query how-to
this was silly from me!
this should naturally look like this:
select case when status ='Closed' then stop_date else start_date end
as adate,
sum(case when status ='Closed' then 1 else 0 end) as
closedCount,
sum(case when status ='New' then 1 else 0 end) as openedCount
from Yourtable
where status in ('Closed','New')
group by case when status ='Closed' then stop_date else start_date end
order by adate
Marc
> Hi,
> What about something like that ?
> select adate, sum(openedCount) as openedCount, sum(closedCount) as
closedCount
> from
> (
> select sum(case when status ='Closed' then stop_date else start_date
end) as adate,
>sum(case when status ='Closed' then 1 else 0 end) as
closedCount
>sum(case when status ='New' then 1 else 0 end) as openedCount
> from Yourtable
> where status in ('Closed','New')
> )x
> group by adate
> order by adate
--
Sent via pgsql-sql mailing list ([email protected])
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-sql
