[SQL] Min and Max
Hello, everybody! I've trouble to make a simple(?) query... The following table is an example: table: children id_father | child_name | child_age --++ 1 | John | 2 1 | Joe| 3 1 | Mary | 4 1 | Cristine | 4 2 | Paul | 1 2 | Stephany | 2 2 | Raul | 5 How can I get the rows of the children name and its father such that they have the min child_ages? I expect the following rows as result: id_father | child_name | child_age --++ 1 | John | 2 2 | Paul | 1 The same for the max child_ages... id_father | child_name | child_age --++ 1 | Mary | 4 1 | Cristine | 4 2 | Raul | 5 I tried to use min() and max() with group by but I could not get the expected results: -- the following does not return the child_name... select id_father, min(child_age) from children group by id_father; select id_father, max(child_age) from children group by id_father; Any suggestions? Thanks In Advance, Sergio Oshiro ---(end of broadcast)--- TIP 1: subscribe and unsubscribe commands go to [EMAIL PROTECTED]
Re: [SQL] Min and Max
On 29 Nov 2002, Sergio Oshiro wrote: How can I get the rows of the children name and its father such that they have the min child_ages? -- the following does not return the child_name... select id_father, min(child_age) from children group by id_father; select id_father, max(child_age) from children group by id_father; You could join one of the above with the table itself and get the result. Something like select * from ( select id_father, min(child_age) from children group by id_father) as r, children where children.id_father = r.id_father and children.min = r.min; -- /Dennis ---(end of broadcast)--- TIP 1: subscribe and unsubscribe commands go to [EMAIL PROTECTED]
Re: [SQL] Min and Max
On Fri, Nov 29, 2002 at 10:55:54AM -0800, Sergio Oshiro wrote: Hello, everybody! I've trouble to make a simple(?) query... The following table is an example: table: children id_father | child_name | child_age --++ 1 | John | 2 1 | Joe| 3 1 | Mary | 4 1 | Cristine | 4 2 | Paul | 1 2 | Stephany | 2 2 | Raul | 5 How can I get the rows of the children name and its father such that they have the min child_ages? I expect the following rows as result: id_father | child_name | child_age --++ 1 | John | 2 2 | Paul | 1 The same for the max child_ages... id_father | child_name | child_age --++ 1 | Mary | 4 1 | Cristine | 4 2 | Raul | 5 select distinct on (id_father) * from children order by id_father, child_age; will give your results select distinct on (id_father) * from children order by id_father, child_age desc; will give the oldest children, but it doesn't list both mary and christine -- it arbitrarily lists mary (you could add child_name to sort order so it wouldn't be abitrary, but it still won't list both). this is a weird use of distinct on, though, and perhaps cheating. a canonical, if slower solution (and one that fixes the tie for oldest child) is: select id_father, child_name, child_age from children c1 where not exists (select * from children c2 where c1.id_father=c2.id_father and c2.child_age c1.child_age); swap the '' to '' for youngest. - J. -- Joel BURTON | [EMAIL PROTECTED] | joelburton.com | aim: wjoelburton Independent Knowledge Management Consultant ---(end of broadcast)--- TIP 3: if posting/reading through Usenet, please send an appropriate subscribe-nomail command to [EMAIL PROTECTED] so that your message can get through to the mailing list cleanly
[SQL] Combining queries while preserving order in SQL - Help!
Hi there, I need to do the following in one SQL query: select field1, field2, field3, field4, field5 from table where field6 5 order by field1 And a totals line which shows the sum for each column. The important part is that I need to preserve the order by of the first query. Is there any way to do this in one query? Thank you, -- Casey Allen Shobe, Open Source Software Solutions [EMAIL PROTECTED] / http://www.osss.net / 770-653-4526 ---(end of broadcast)--- TIP 6: Have you searched our list archives? http://archives.postgresql.org
Re: [SQL] CURRENT_TIMSTAMP
On Mon, Dec 02, 2002 at 11:41:33AM -0500, Raymond Chui wrote:
I created a column, dada type timstamp with time zone
and with default CURRENT_TIMSTAMP
it shows me the default is
default ('now'::text)::timstamp(6) with time zone
Then when I insert a row, the default timestamp value is
-mm-dd HH:MM:ss.m+00
where m is milliseconds.
How do I make default only -mm-dd HH:MM:ss+00 ?
Thank Q!
The problem isn't CURRENT_TIMESTAMP, it's your table definition.
If you create the field as timestamp(0) [in 7.3, that's timestamp(0)
with time zone, since the default has swung to no-TZ], it will keep
track of just HMS. Or put in other values for 0 for more granularity on
seconds.
Of course, you can always store the specific time and select it out
w/less resolution (using the time/date functions). In some cases, this
might be a better solution.
- J.
--
Joel BURTON | [EMAIL PROTECTED] | joelburton.com | aim: wjoelburton
Independent Knowledge Management Consultant
---(end of broadcast)---
TIP 1: subscribe and unsubscribe commands go to [EMAIL PROTECTED]
Re: [SQL] CURRENT_TIMSTAMP
On Mon, 2 Dec 2002, Raymond Chui wrote:
I created a column, dada type timstamp with time zone
and with default CURRENT_TIMSTAMP
it shows me the default is
default ('now'::text)::timstamp(6) with time zone
Then when I insert a row, the default timestamp value is
-mm-dd HH:MM:ss.m+00
where m is milliseconds.
How do I make default only -mm-dd HH:MM:ss+00 ?
As already suggested you probably want a timestamp with time zone(0)
column, but if you really want the ability to insert partial seconds but
the default to not have partial seconds, you can use
CURRENT_TIMESTAMP(0) I believe.
---(end of broadcast)---
TIP 6: Have you searched our list archives?
http://archives.postgresql.org
[SQL] CURRENT_TIMSTAMP
I created a column, dada type timstamp with time zone
and with default CURRENT_TIMSTAMP
it shows me the default is
default ('now'::text)::timstamp(6) with time zone
Then when I insert a row, the default timestamp value is
-mm-dd HH:MM:ss.m+00
where m is milliseconds.
How do I make default only -mm-dd HH:MM:ss+00 ?
Thank Q!
begin:vcard
n:Chui;Raymond
tel;work:301-713-0640 x168
x-mozilla-html:FALSE
url:http://www.nws.noaa.gov/
org:NWS, NOAA;OHD13
version:2.1
email;internet:[EMAIL PROTECTED]
title:CS
adr;quoted-printable:;;1325 East-West Highway=0D=0ASSMC2, Room 8112;Silver Spring;MD;20910-3282;U.S.A.
fn:Raymond Chui
end:vcard
---(end of broadcast)---
TIP 1: subscribe and unsubscribe commands go to [EMAIL PROTECTED]
