Thread: Postgres-sql-php
<div dir="ltr">Hi Everybody..<br /><br />Let's present my problem:<br /><br />I have a table named <b>t1</b> and i will
insertdifferents values like this :<br /><br />insert into t1 (num,father,child,age) values ('1','joe','bruce','14',);
<br/> insert into t1 (num,father,child,age) values ('1','joe','lei','10',); <br />insert into t1 (num,father,child,age)
values('1','joe','mike','5',); <br /><br />when i use select * from t1 i obtain:<br /><br /><u>num father child
age</u><br/>1 joe bruce 14<br />1 joe lei 10<br />1 joe mike 5<br /><br
/><br/>i want to have <br /><br /><u>num father child age</u><br />1 joe bruce 14<br />
lei 10<br /> mike 5<br /><br />what can i do as select request to obtain this
capture?<br/><br />Thanks :)<br /></div>
Howdy, Zied.
The query below outputs the results as you want, but
I suspect you have a more general situation you want to solve.
If you have more than one father, say "manuel", you would want something like this ?
num father child age
1 joe bruce 14
lei 10
mike 5
1 joe bruce 14
lei 10
mike 5
2 manuel child1 35
child2 33
child3 30
Confirm, please .
Also, do you want the output ordered by age? always?
If so , tell me and we can tweak a little the query to best-fit your needs
Best,
Oliveiros
SELECT a.num,a.father,b.child,b.age
FROM
(
SELECT num,father, MAX(age)as maximo
FROM
(
SELECT num,father, MAX(age)as maximo
FROM t1
GROUP BY num,father) a
RIGHT JOIN t1 b
ON b.age = a.maximo
GROUP BY num,father) a
RIGHT JOIN t1 b
ON b.age = a.maximo
----- Original Message -----From: Zied KharratSent: Thursday, October 23, 2008 9:14 AMSubject: [SQL] Postgres-sql-phpHi Everybody..
Let's present my problem:
I have a table named t1 and i will insert differents values like this :
insert into t1 (num,father,child,age) values ('1','joe','bruce','14',);
insert into t1 (num,father,child,age) values ('1','joe','lei','10',);
insert into t1 (num,father,child,age) values ('1','joe','mike','5',);
when i use select * from t1 i obtain:
num father child age
1 joe bruce 14
1 joe lei 10
1 joe mike 5
i want to have
num father child age
1 joe bruce 14
lei 10
mike 5
what can i do as select request to obtain this capture?
Thanks :)
hi,
i don't want any sort.. just like this example
this is what i want really..
What can be then the sql request without sort with this update.. Thanks :)
i don't want any sort.. just like this example
num father child age col5
1 joe bruce 14 8
lei 10
mike 5
1 joe bruce 14 8
lei 10
mike 5
2 manuel child1 35 16
child2 33
child3 30
this is what i want really..
What can be then the sql request without sort with this update.. Thanks :)
Just add two conditions on the previous query
A particularity of this approach is that the non-null record will always appear with the first child in alphabetical order.
But, according to what you explain, I guess it is OK, and if it doesn't it is easily changed. :-)
Also I've changed the first condition on the right outer join coz it would give trouble if two childs happened to be of the same age.
I guess it will never happen two childs with the same name :p
Best,
Oliveiros
SELECT a.num,a.father,b.child,b.age
FROM
(
SELECT num,father, MIN(child)as primeira
FROM t1
GROUP BY num,father) a
RIGHT JOIN t1 b
ON b.child = a.primeira
AND b.father = a.father
AND a.num = b.num
-- FROM
(
SELECT num,father, MIN(child)as primeira
FROM t1
GROUP BY num,father) a
RIGHT JOIN t1 b
ON b.child = a.primeira
AND b.father = a.father
AND a.num = b.num
even the biggest failure, even the worst mistake, beats the hell out of never trying...
- Meredith Grey
2008/10/23 Zied Kharrat <khzied@gmail.com>
hi,
i don't want any sort.. just like this examplenum father child age col5
1 joe bruce 14 8
lei 10
mike 52 manuel child1 35 16child2 33child3 30
this is what i want really..
What can be then the sql request without sort with this update.. Thanks :)
<br /><font face="Courier New" size="2"><div class="Ih2E3d">I guess you can change a little the query to your needs.<br />Theproblem is pretty much the same...<br />I've used c3 column in equality, but if this column<br />has repeated values,just choose any column or combination of columns which is unique.<br /><br />Best,<br />Oliveiros<br /><br />SELECTa.<font color="#ff0000">c1</font><span style="color: rgb(255, 102, 102);"></span>,a.<span style="color: rgb(255,0, 0);">c2</span>,b.<span style="color: rgb(255, 102, 102);">c3</span>,b.c4,<font color="#ff0000">a.c5,</font><fontcolor="#000000">b.c6</font><br /> FROM<br />(<br /></div>SELECT c1,c2,c5, MIN(c3)as primeira<divclass="Ih2E3d">FROM t1<br />GROUP BY c1,c2,c5<br />) a<br />RIGHT JOIN t1 b<br /></div>ON b.c3 = a.primeira<br/>AND b.c1 = a.c1<br />AND a.c2 = b.c2<br />AND a.c5 = b.c5<br /></font><br /><div class="gmail_quote">2008/10/23Zied Kharrat <span dir="ltr"><<a href="mailto:khzied@gmail.com">khzied@gmail.com</a>></span><br/><blockquote class="gmail_quote" style="border-left: 1pxsolid rgb(204, 204, 204); margin: 0pt 0pt 0pt 0.8ex; padding-left: 1ex;"><div dir="ltr">Really, i have this schema:<br/><br /><u>c1 c2 c3 c4 c5 c6</u><br /><b>v1</b> <b>v2</b> v3 v4 <b>v5</b> v6<br /><b>v1</b> <b>v2</b> v7 v8 <b>v5</b> v9<br /><b>v1</b> <b>v2</b> v10 v11 <b>v5</b> v12<br /><br />how can i do my sql request to obtain this?<br /><br /><u>c1 c2 c3 c4 c5 c6</u><br /><b>v1</b> <b>v2</b> v3 v4 <b>v5</b> v6<br /><b> </b> v7 v8 <b> </b> v9<br /><b> </b> v10 v11 <b> </b> v12<br /><br /><br />Thanku very much :)<br /></div></blockquote></div><br /><br clear="all" /><br />-- <br />even the biggest failure, eventhe worst mistake, beats the hell out of never trying...<br />- Meredith Grey<br /><br />