Thread: challenging query
Consider the following table: A B C D select? ------------------------------- 1 FOO A1 100 n 1 BAR Z2 100 n 2 FOO A1 101 y 2 BAR Z2 101 y 3 FOO A1 102 y 4 BAR Z2 99 y 5 FOO A1 99 n 6 BAR Z2 98 n 7 FOO AB 103 y 7 BAR ZY 103 y This table has the idea of "groups", that is, a group is defined as all of the words from B that have the same number A. The values in column C also matter- we want to select both groups A=7 and A=1 since they contain different values C. Note that the groups defined by A=1 and A=3 are distinct- they do not contain the same number of words from B, so we want to select them both. Also note that D is datetime, and all the rows with the same number A will have the same D (this is actually ensured by a single row in another table.) I want to select all of the numbers A which define distinct groups and have the highest datetime D. Is this possible in a SQL query?
> > Consider the following table: > > A B C D select? > ------------------------------- > 1 FOO A1 100 n > 1 BAR Z2 100 n > 2 FOO A1 101 y > 2 BAR Z2 101 y > 3 FOO A1 102 y > 4 BAR Z2 99 y > 5 FOO A1 99 n > 6 BAR Z2 98 n > 7 FOO AB 103 y > 7 BAR ZY 103 y > > This table has the idea of "groups", that is, a group is defined as > all of the words from B that have the same number A. The values in > column C also matter- we want to select both groups A=7 and A=1 since > they contain different values C. Note that the groups defined by A=1 > and A=3 are distinct- they do not contain the same number of words > from B, so we want to select them both. Also note that D is datetime, > and all the rows with the same number A will have the same D (this is > actually ensured by a single row in another table.) > > I want to select all of the numbers A which define distinct groups and > have the highest datetime D. Is this possible in a SQL query? Sorry, there is an inconsistency in your question, at least to me. In the table's last column you say you want to select A in (2,3,4,7) but in the epilogue you say you want to select A in (7,1,1,3) What did I miss? Regards, Christoph
> > Consider the following table: > > A B C D select? > ------------------------------- > 1 FOO A1 100 n > 1 BAR Z2 100 n > 2 FOO A1 101 y > 2 BAR Z2 101 y > 3 FOO A1 102 y > 4 BAR Z2 99 y > 5 FOO A1 99 n > 6 BAR Z2 98 n > 7 FOO AB 103 y > 7 BAR ZY 103 y > > This table has the idea of "groups", that is, a group is defined as > all of the words from B that have the same number A. The values in > column C also matter- we want to select both groups A=7 and A=1 since > they contain different values C. Note that the groups defined by A=1 > and A=3 are distinct- they do not contain the same number of words > from B, so we want to select them both. Also note that D is datetime, > and all the rows with the same number A will have the same D (this is > actually ensured by a single row in another table.) > > I want to select all of the numbers A which define distinct groups and > have the highest datetime D. Is this possible in a SQL query? > Now that I've read your request more attentively, I understand what you want. But I have to admit I have no idea how to word the query, I even don't know if it's possible at all. Regards, Christoph
On Fri, 05 Oct 2001 17:03:41 METDST
Haller Christoph wrote:
> >
> > Consider the following table:
> >
> > A B C D select?
> > -------------------------------
> > 1 FOO A1 100 n
> > 1 BAR Z2 100 n
> > 2 FOO A1 101 y
> > 2 BAR Z2 101 y
> > 3 FOO A1 102 y
> > 4 BAR Z2 99 y
> > 5 FOO A1 99 n
> > 6 BAR Z2 98 n
> > 7 FOO AB 103 y
> > 7 BAR ZY 103 y
> >
> > This table has the idea of "groups", that is, a group is defined as
> > all of the words from B that have the same number A. The values in
> > column C also matter- we want to select both groups A=7 and A=1 since
> > they contain different values C. Note that the groups defined by A=1
> > and A=3 are distinct- they do not contain the same number of words
> > from B, so we want to select them both. Also note that D is datetime,
> > and all the rows with the same number A will have the same D (this is
> > actually ensured by a single row in another table.)
> >
> > I want to select all of the numbers A which define distinct groups and
> > have the highest datetime D. Is this possible in a SQL query?
> >
> Now that I've read your request more attentively, I understand what
> you want. But I have to admit I have no idea how to word the query,
> I even don't know if it's possible at all.
> Regards, Christoph
>
I also haven't satisfactorily understood the mean of the epilogue,but I probably think he wanted to account for the
followingtable which is separated into "groups".
A B C D select?
------------------------------------
1 FOO A1 100 n
1 BAR Z2 100 n
2 FOO A1 101 y
2 BAR Z2 101 y
---------------------------------
5 FOO A1 99 n
3 FOO A1 102 y
---------------------------------
6 BAR Z2 98 n
4 BAR Z2 99 y
---------------------------------
7 FOO AB 103 y
7 BAR ZY 103 y
for instance:
select u0.A, u0.B, u0.C, u0.D from (select t0.*, t1.cnt from (select a, count(*) as cnt from
test_table group by a ) as t1 inner join test_table as t0 on(t0.a = t1.a)
) as u0 where not exists (select u1.* from (select t0.*, t1.cnt
from (select a, count(*) as cnt from test_table
group by a ) as t1 inner join test_table as t0
on(t0.a= t1.a) ) as u1 where u1.cnt = u0.cnt
and u1.a != u0.a and u1.d > u0.d and u1.b = u0.b
and u1.c = u0.c )
;
a | b | c | d
---+-----+----+-----2 | BAR | Z2 | 1012 | FOO | A1 | 1013 | FOO | A1 | 1024 | BAR | Z2 | 997 | BAR | ZY | 1057 | FOO |
AB| 105
(6 rows)
Have a nice weekend!
----------------------
Masaru Sugawara
On Sat, 06 Oct 2001 18:54:21 +0900 Masaru Sugawara wrote: > > A B C D select? > ------------------------------------ > 1 FOO A1 100 n > 1 BAR Z2 100 n > 2 FOO A1 101 y > 2 BAR Z2 101 y > --------------------------------- > 5 FOO A1 99 n > 3 FOO A1 102 y > --------------------------------- > 6 BAR Z2 98 n > 4 BAR Z2 99 y > --------------------------------- > 7 FOO AB 103 y > 7 BAR ZY 103 y > > > select u0.A, u0.B, u0.C, u0.D > from (select t0.*, t1.cnt > from (select a, count(*) as cnt > from test_table > group by a ) as t1 > inner join test_table as t0 > on(t0.a = t1.a) > ) as u0 > where not exists (select u1.* > from (select t0.*, t1.cnt > from (select a, count(*) as cnt > from test_table > group by a ) as t1 > inner join test_table as t0 > on(t0.a = t1.a) > ) as u1 > where u1.cnt = u0.cnt > and u1.a != u0.a > and u1.d > u0.d > and u1.b = u0.b > and u1.c = u0.c > ) > ; I noticed there were two vain subselects in the querywhen I had checked past queries by an EXPLAIN, andgave a small changeto the query. -- on 7.1.2 select u0.*, u1.cnt from (select a, count(*) as cnt from test_table group by a ) as u1 inner join test_tableas u0 on(u0.a = u1.a) where not exists (select t0.*, t1.cnt from (select a, count(*) ascnt from test_table group by a) as t1 inner join test_tableas t0 on(t0.a = t1.a) where t1.cnt = u1.cnt and t0.a != u0.a and t0.d > u0.d and t0.b = u0.b and t0.c = u0.c ) ; Masaru Sugawara
Hi, I need some help with the function below. My intention is to hand the content of result_store and sort it out so that I can have a complete recordset. But for some reason result_store is returned empty. It works fine for one row but the concatenation of result_store and result does not seem to work. Can some one pleas explain why? CREATE FUNCTION f_snus() RETURNS varchar AS ' DECLARE result varchar; result_store varchar; row record; BEGIN FOR row IN SELECT snus_id, namn, betyg FROM snus ORDER BY betyg LOOP SELECTINTO result row.snus_id || ''\t'' || row.namn || ''\t'' || row.betyg || ''\n''; result_store := result_store || result; END LOOP; RETURN result_store; END; ' LANGUAGE 'PLPGSQL'; Regards Torbjörn Andersson --------------------------------------------------- Embryo Communication phone: +46 (0) 31-774 39 11(00) Kungsgatan 7a fax: +46 (0)31 774 07 80 S-411 19 Göteborg mobile: 0708-30 70 04 Sweden home: http://www.embryo.se/ mail: torbjorn.andersson@embryo.se --------------------------------------------------- "Att idag tänka annorlunda än igår skiljer den vise från den envise." John Steinbeck
You are concatenating with a null value, which causes the result to be null. See below, I have fixed it. Regards, Aasmund On Tue, 30 Oct 2001 22:05:15 +0100, Torbjörn Andersson <tobbe@embryo.se> wrote: > Hi, > > I need some help with the function below. My intention is to hand the > content of result_store and sort it out so that I can have a complete > recordset. But for some reason result_store is returned empty. It works fine > for one row but the concatenation of result_store and result does not seem > to work. > > Can some one pleas explain why? > > > > CREATE FUNCTION f_snus() > RETURNS varchar > AS > ' DECLARE > result varchar := ''''; > result_store varchar := ''''; > row record; > BEGIN > FOR row IN > SELECT snus_id, namn, betyg FROM snus ORDER BY betyg > LOOP > SELECT INTO result row.snus_id || ''\t'' || row.namn || > ''\t'' || row.betyg || ''\n''; > result_store := result_store || result; > END LOOP; > RETURN result_store; > END; > ' LANGUAGE 'PLPGSQL'; > > > > Regards > > Torbjörn Andersson > --------------------------------------------------- > Embryo Communication phone: +46 (0) 31-774 39 11(00) > Kungsgatan 7a fax: +46 (0)31 774 07 80 > S-411 19 Göteborg mobile: 0708-30 70 04 > Sweden home: http://www.embryo.se/ > mail: torbjorn.andersson@embryo.se > --------------------------------------------------- > "Att idag tänka annorlunda än igår skiljer den vise från den envise." - John > Steinbeck > > > ---------------------------(end of broadcast)--------------------------- > TIP 6: Have you searched our list archives? > > http://archives.postgresql.org Aasmund Midttun Godal aasmund@godal.com - http://www.godal.com/ +47 40 45 20 46
Torbjörn Andersson <tobbe@embryo.se> writes:
> I need some help with the function below. My intention is to hand the
> content of result_store and sort it out so that I can have a complete
> recordset. But for some reason result_store is returned empty. It works fine
> for one row but the concatenation of result_store and result does not seem
> to work.
Try initializing result_store to something other than NULL ...
regards, tom lane