Thread: Modifying selected records
Hi, I've got a subselect which output is this: sorszam(int);all_kod(varchar);nev(varchar);megall(int);erkezik_ido(time);indul_ido(time);train_selector(varchar) 0;"5510017";"Budapest-Keleti pu.";1;"06:10:00";"06:10:00";"910_365334" ... 21;"5501289";"Győr";1;"07:39:00";"07:39:00";"910_365334" 0;"5501289";"Győr";1;"07:41:00";"07:41:00";"910_311546" ... 5;"5544800";"Csorna";1;"08:00:00";"08:00:00";"910_311546" 0;"5544800";"Csorna";1;"08:12:00";"08:12:00";"910_365339" ... 8;"5502246";"Szombathely";1;"09:02:00";"09:02:00";"910_365339" I would like this: ... x;"5501289";"Győr";1;"07:39:00";"07:41:00";"910_xxxxxx" ... y;"5544800";"Csorna";1;"08:00:00";"08:12:00";"910_yyyyyy" ... and I'd like recount the sorszam continously from 0 to 34. Can I do this with SQL or I need PL/pgPerl, for example? TIA, Ruzsi
Howdy, Ruzsi. What do u mean by "recount the sorszam continously from 0 to 34" ? The sorszam is only allowed to take values on that range? Is That ? Best, Oliveiros ----- Original Message ----- From: "Ruzsinszky Attila" <ruzsinszky.attila@gmail.com> To: <pgsql-novice@postgresql.org> Sent: Wednesday, September 02, 2009 9:42 AM Subject: [NOVICE] Modifying selected records Hi, I've got a subselect which output is this: sorszam(int);all_kod(varchar);nev(varchar);megall(int);erkezik_ido(time);indul_ido(time);train_selector(varchar) 0;"5510017";"Budapest-Keleti pu.";1;"06:10:00";"06:10:00";"910_365334" ... 21;"5501289";"Győr";1;"07:39:00";"07:39:00";"910_365334" 0;"5501289";"Győr";1;"07:41:00";"07:41:00";"910_311546" ... 5;"5544800";"Csorna";1;"08:00:00";"08:00:00";"910_311546" 0;"5544800";"Csorna";1;"08:12:00";"08:12:00";"910_365339" ... 8;"5502246";"Szombathely";1;"09:02:00";"09:02:00";"910_365339" I would like this: ... x;"5501289";"Győr";1;"07:39:00";"07:41:00";"910_xxxxxx" ... y;"5544800";"Csorna";1;"08:00:00";"08:12:00";"910_yyyyyy" ... and I'd like recount the sorszam continously from 0 to 34. Can I do this with SQL or I need PL/pgPerl, for example? TIA, Ruzsi -- Sent via pgsql-novice mailing list (pgsql-novice@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-novice
Hi Oviverio, > What do u mean by "recount the sorszam continously from 0 to 34" ? As you can see I've got three ranges: 0-21; 0-5 and 0-8. In the result I'd like a "sorszam" from 0-34. So I want "recount" the records (only in my result! DB is remaining the same). > The sorszam is only allowed to take values on that range? Is That ? No. The range itself is not important. I want to "join" the 3 ranges in one. But not the sorszam is the most important for me. TIA, Ruzsi
I see. Does your output have any kind of order? For ex, by the arrival/departure time of the train, or by registration number or something? Is that a big listing? Or it produces just a few dozens records? Best, Oliveiros ----- Original Message ----- From: "Ruzsinszky Attila" <ruzsinszky.attila@gmail.com> To: "Oliveiros C," <oliveiros.cristina@marktest.pt> Cc: <pgsql-novice@postgresql.org> Sent: Wednesday, September 02, 2009 10:55 AM Subject: Re: [NOVICE] Modifying selected records > Hi Oviverio, > >> What do u mean by "recount the sorszam continously from 0 to 34" ? > As you can see I've got three ranges: 0-21; 0-5 and 0-8. > In the result I'd like a "sorszam" from 0-34. So I want "recount" the > records > (only in my result! DB is remaining the same). > >> The sorszam is only allowed to take values on that range? Is That ? > No. > The range itself is not important. I want to "join" the 3 ranges in one. > But not the sorszam is the most important for me. > > TIA, > Ruzsi
Well, one way to do it with just sql , AFAIK, at least, is to use something like (untested) SELECT COUNT(query2.*), query1.all_kod,quey1.nev,query1.megall,query1.erkezik_ido,query1.indul_ido,'910_' || COUNT(query2.*) FROM (/*your query goes here */) query1, (/*and here */) query2 WHERE query1.indul_ido >= query2.indul_ido Try this out, then tell me if it worked I'm Assuming that no two trains depart at the same time. Is this true? Also, be aware that this is quite slow when the output becomes too extense, and it is suitable only for small outputs. Best, Oliveiros PS- Always include the mailing list in CC, as someone more knowledgeable than me may help you faster and/or better ----- Original Message ----- From: "Ruzsinszky Attila" <ruzsinszky.attila@gmail.com> To: "Oliveiros C," <oliveiros.cristina@marktest.pt> Sent: Wednesday, September 02, 2009 11:41 AM Subject: Re: [NOVICE] Modifying selected records >> Does your output have any kind of order? > Yes. > >> For ex, by the arrival/departure time of the train, or by registration >> number or something? > Now: order by indul_ido (means: departure time). > >> Is that a big listing? Or it produces just a few dozens records? > I think no. The give example is 37 records. I think the maximum will be > 100-150-200 records. > > Bye: > Ruzsi
> SELECT COUNT(query2.*), > query1.all_kod,quey1.nev,query1.megall,query1.erkezik_ido,query1.indul_ido,'910_' > || COUNT(query2.*) > FROM (/*your query goes here */) query1, (/*and here */) query2 > WHERE query1.indul_ido >= query2.indul_ido Here is my SELECT first: SELECT sorszam, all_kod, nev, megall, erkezik_ido, indul_ido, train_selector FROM menetrend, stat where statkod=all_kod and train_selector in (SELECT train_selector FROM service where vonatszam='910' and datum_ig>now() and datum_tol<now() order by datum_ig desc, datum_tol desc) order by indul_ido When I run that I got what I put here. After this I combined your version with my select: ( I put my SELECT mechanically twice in /*your query goes here */ parts.) SELECT COUNT(query2.*), query1.all_kod,query1.nev,query1.megall,query1.erkezik_ido,query1.indul_ido,'910_' || COUNT(query2.*) FROM (SELECT sorszam, all_kod, nev, megall, erkezik_ido, indul_ido, train_selector FROM menetrend, stat where statkod=all_kod and train_selector in (SELECT train_selector FROM service where vonatszam='910' and datum_ig>now() and datum_tol<now() order by datum_ig desc, datum_tol desc) order by indul_ido) query1, (SELECT sorszam, all_kod, nev, megall, erkezik_ido, indul_ido, train_selector FROM menetrend, stat where statkod=all_kod and train_selector in (SELECT train_selector FROM service where vonatszam='910' and datum_ig>now() and datum_tol<now() order by datum_ig desc, datum_tol desc) order by indul_ido) query2 WHERE query1.indul_ido >= query2.indul_ido The result is: ERROR: column "query1.all_kod" must appear in the GROUP BY clause or be used in an aggregate function In my SELECT only one train was selected. TIA, Ruzsi
I see. I completely 4got the GROUP BY clause Add GROUP BY query1.sorszam, query1.all_kod, query1.nev, query1.megall, query1.erkezik_ido, query1.indul_ido, query1.train_selector to the query I gave you. And try again Best, Oliveiros ----- Original Message ----- From: "Ruzsinszky Attila" <ruzsinszky.attila@gmail.com> To: "Oliveiros C," <oliveiros.cristina@marktest.pt> Cc: "postgresql novice" <pgsql-novice@postgresql.org> Sent: Wednesday, September 02, 2009 1:10 PM Subject: Re: [NOVICE] Modifying selected records > SELECT COUNT(query2.*), > query1.all_kod,quey1.nev,query1.megall,query1.erkezik_ido,query1.indul_ido,'910_' > || COUNT(query2.*) > FROM (/*your query goes here */) query1, (/*and here */) query2 > WHERE query1.indul_ido >= query2.indul_ido Here is my SELECT first: SELECT sorszam, all_kod, nev, megall, erkezik_ido, indul_ido, train_selector FROM menetrend, stat where statkod=all_kod and train_selector in (SELECT train_selector FROM service where vonatszam='910' and datum_ig>now() and datum_tol<now() order by datum_ig desc, datum_tol desc) order by indul_ido When I run that I got what I put here. After this I combined your version with my select: ( I put my SELECT mechanically twice in /*your query goes here */ parts.) SELECT COUNT(query2.*), query1.all_kod,query1.nev,query1.megall,query1.erkezik_ido,query1.indul_ido,'910_' || COUNT(query2.*) FROM (SELECT sorszam, all_kod, nev, megall, erkezik_ido, indul_ido, train_selector FROM menetrend, stat where statkod=all_kod and train_selector in (SELECT train_selector FROM service where vonatszam='910' and datum_ig>now() and datum_tol<now() order by datum_ig desc, datum_tol desc) order by indul_ido) query1, (SELECT sorszam, all_kod, nev, megall, erkezik_ido, indul_ido, train_selector FROM menetrend, stat where statkod=all_kod and train_selector in (SELECT train_selector FROM service where vonatszam='910' and datum_ig>now() and datum_tol<now() order by datum_ig desc, datum_tol desc) order by indul_ido) query2 WHERE query1.indul_ido >= query2.indul_ido The result is: ERROR: column "query1.all_kod" must appear in the GROUP BY clause or be used in an aggregate function In my SELECT only one train was selected. TIA, Ruzsi
Hi, Here is the whole stetement: SELECT COUNT(query2.*), query1.all_kod,query1.nev,query1.megall,query1.erkezik_ido,query1.indul_ido '910_' || COUNT(query2.*) FROM (SELECT sorszam, all_kod, nev, megall, erkezik_ido, indul_ido, train_selector FROM menetrend, stat where statkod=all_kod and train_selector in (SELECT train_selector FROM service where vonatszam='910' and datum_ig>now() and datum_tol<now() order by datum_ig desc, datum_tol desc) order by indul_ido) query1, (SELECT sorszam, all_kod, nev, megall, erkezik_ido, indul_ido, train_selector FROM menetrend, stat where statkod=all_kod and train_selector in (SELECT train_selector FROM service where vonatszam='910' and datum_ig>now() and datum_tol<now() order by datum_ig desc, datum_tol desc) order by indul_ido) query2 WHERE query1.indul_ido >= query2.indul_ido GROUP BY query1.sorszam, query1.all_kod, query1.nev, query1.megall, query1.erkezik_ido, query1.indul_ido, query1.train_selector The result is: ERROR: schema "query1" does not exist I think I lost the thread ... this is too comlplex for me. TIA, Ruzsi
Hi, I tried to understand your SQL and to follow up the order what you wrote. Now there is a syntax error free command: SELECT COUNT(query2.*), query1.all_kod,query1.nev,query1.megall,query1.erkezik_ido,query1.indul_ido,'910_' || COUNT(query2.*) FROM (SELECT sorszam, all_kod, nev, megall, erkezik_ido, indul_ido, train_selector FROM menetrend, stat where statkod=all_kod and train_selector in (SELECT train_selector FROM service where vonatszam='910' and datum_ig>now() and datum_tol<now() order by datum_ig desc, datum_tol desc) order by indul_ido) query1, (SELECT sorszam, all_kod, nev, megall, erkezik_ido, indul_ido, train_selector FROM menetrend, stat where statkod=all_kod and train_selector in (SELECT train_selector FROM service where vonatszam='910' and datum_ig>now() and datum_tol<now() order by datum_ig desc, datum_tol desc) order by indul_ido) query2 WHERE query1.indul_ido >= query2.indul_ido GROUP BY query1.sorszam, query1.all_kod, query1.nev, query1.megall, query1.erkezik_ido, query1.indul_ido, query1.train_selector (I don't understand in the whole but is working.) And here is the result: 9;"5547746";"Szárligeti elágazás";0;"06:53:00";"06:53:00";"910_9" 37;"5502246";"Szombathely";1;"09:02:00";"09:02:00";"910_37" 15;"5501214";"Komárom-Rendező";0;"07:16:00";"07:16:00";"910_15" 29;"5544800";"Csorna";1;"08:12:00";"08:12:00";"910_29" 18;"5501248";"Nagyszentjános";0;"07:27:00";"07:27:00";"910_18" 5;"5501057";"Biatorbágy";0;"06:35:00";"06:35:00";"910_5" 30;"5502378";"Szil-Sopronnémeti";0;"08:19:00";"08:19:00";"910_30" 3;"5501024";"Budapest-Kelenföld";1;"06:24:00";"06:25:00";"910_3" 24;"5548009";"Győr-GYSEV nyugati elág.";0;"07:43:00";"07:43:00";"910_24" 17;"5501230";"Ács";0;"07:22:00";"07:22:00";"910_17" 27;"5502543";"Kóny";0;"07:54:00";"07:54:00";"910_27" 4;"5501032";"Budaörs";0;"06:29:00";"06:29:00";"910_4" 32;"5502436";"Répcelak";0;"08:32:00";"08:32:00";"910_32" 7;"5501081";"Bicske";0;"06:44:00";"06:44:00";"910_7" 8;"5501107";"Szárliget";0;"06:50:00";"06:50:00";"910_8" 31;"5502402";"Beled";0;"08:27:00";"08:27:00";"910_31" 34;"5502485";"Ölbő-Alsószeleste";0;"08:44:00";"08:44:00";"910_34" 1;"5510017";"Budapest-Keleti pu.";1;"06:10:00";"06:10:00";"910_1" 2;"5510025";"Budapest-Ferencváros";0;"06:19:00";"06:19:00";"910_2" 22;"5501289";"Győr";1;"07:39:00";"07:39:00";"910_22" 26;"5502535";"Enese";0;"07:51:00";"07:51:00";"910_26" 19;"5501255";"Győrszentiván";0;"07:32:00";"07:32:00";"910_19" 12;"5501164";"Tata";0;"07:05:00";"07:05:00";"910_12" 35;"5502220";"Porpác";0;"08:49:00";"08:49:00";"910_35" 6;"5501065";"Herceghalom";0;"06:39:00";"06:39:00";"910_6" 21;"5501271";"Győr-Rendező";0;"07:37:00";"07:37:00";"910_21" 23;"5501289";"Győr";1;"07:41:00";"07:41:00";"910_23" 28;"5544800";"Csorna";1;"08:00:00";"08:00:00";"910_28" 33;"5502469";"Hegyfalu";0;"08:39:00";"08:39:00";"910_33" 16;"5501222";"Komárom";0;"07:17:00";"07:17:00";"910_16" 14;"5501180";"Almásfüzitő felső";0;"07:12:00";"07:12:00";"910_14" 13;"5501172";"Almásfüzitő";0;"07:10:00";"07:10:00";"910_13" 36;"5502238";"Vép";0;"08:55:00";"08:55:00";"910_36" 10;"5547753";"Tatabányai elágazás";0;"06:55:00";"06:55:00";"910_10" 25;"5502519";"Ikrény";0;"07:47:00";"07:47:00";"910_25" 20;"5540766";"Győrszentiváni elág.";0;"07:35:00";"07:35:00";"910_20" 11;"5501131";"Tatabánya";1;"06:58:00";"06:59:00";"910_11" I think I need at least an order by count or indul_ido. Where do I have to put it? Other problem is these two records: 23;"5501289";"Győr";1;"07:41:00";"07:41:00";"910_23" 28;"5544800";"Csorna";1;"08:00:00";"08:00:00";"910_28" It is correct we eliminated the double record but it is not enough! I need to modify the erkezik_ido and/or indul_ido, too from the eliminated records. I'm very glad you try to help me! TIA, Ruzsi
Hi, > I think I need at least an order by count or indul_ido. Where do I have to > put it? I found the answer: at the end of the statement. ;-) > Other problem is these two records: > 23;"5501289";"Győr";1;"07:41:00";"07:41:00";"910_23" > 28;"5544800";"Csorna";1;"08:00:00";"08:00:00";"910_28" > > It is correct we eliminated the double record but it is not enough! After order by count I found my previous sentence wasn't true! So we have to focus to this problem: 22;"5501289";"Győr";1;"07:39:00";"07:39:00";"910_22" 23;"5501289";"Győr";1;"07:41:00";"07:41:00";"910_23" 28;"5544800";"Csorna";1;"08:00:00";"08:00:00";"910_28" 29;"5544800";"Csorna";1;"08:12:00";"08:12:00";"910_29" Now I revised the result records. I don't want to change the train_selector except the duplicated records. I think you misunderstood my task. Train_selector has to be modified because the duplicated record will be merged in only one record and the original train_selector related to the original records. TIA, Ruzsi
Hi, ('910_' || COUNT(query2.*)) as "train_count", From that part of SQL SELECT command how can I get 910_ if 910 comes from service.vonatszam? TIA, Ruzsi
Hi, Why I can't use this: SELECT sorszam, all_kod, nev, megall, erkezik_ido, case when erkezik_ido=indul_ido then '-' else erkezik_ido end as "E:", indul_ido, train_selector TIA, Ruzsi
Hi, Ruzsinszky
So we have to focus to this problem:
22;"5501289";"Győr";1;"07:39:00";"07:39:00";"910_22"
23;"5501289";"Győr";1;"07:41:00";"07:41:00";"910_23"
28;"5544800";"Csorna";1;"08:00:00";"08:00:00";"910_28"
29;"5544800";"Csorna";1;"08:12:00";"08:12:00";"910_29"
Now I revised the result records. I don't want to change the
train_selector except the duplicated records. I think you
misunderstood my task. Train_selector has to be modified
because the duplicated record will be merged in only one
record and the original train_selector related to the original
records.
Indeed, I am not sure if I am understanding what you need.
By duplicated record you mean records that have the first three columns the same ? E.G. ;"5544800";"Csorna";1; ?
And in these cases what do you need the train selector to be exactly?
The left part of it (910) means what exactly?
Best,
Oliveiros
Hi, > 22;"5501289";"Győr";1;"07:39:00";"07:39:00";"910_22" > 23;"5501289";"Győr";1;"07:41:00";"07:41:00";"910_23" I'd like this: <next count>; "5501289";"Győr"; "07:39:00"; "07:41:00"; "910_unknown" > 28;"5544800";"Csorna";1;"08:00:00";"08:00:00";"910_28" > 29;"5544800";"Csorna";1;"08:12:00";"08:12:00";"910_29" For Csorna: <next count>;"5544800";"Csorna";1;"08:00:00";"08:12:00";"910_unknown2" _unknown means: it is a programmed selector, so not valid. As you can see I want to merge the two time date in one record. The problem comes from some specification misunderstood. This timetable comes from three (almos) independent XML files. > Indeed, I am not sure if I am understanding what you need. Ihope you understand now. > By duplicated record you mean records that have the first three columns the > same ? E.G. ;"5544800";"Csorna";1; ? Csorna and Győr is the problematic. > And in these cases what do you need the train selector to be exactly? I don't know exactly. I'm sure I have to change it because in the merged record the sector is not valid. > The left part of it (910) means what exactly? vonatszam. In English: train number. TIA, Ruzsi
Hello again, Ruzsi.
In what concerns to merging records,
I think That can be done by tweaking ur query a little (N.B: Your query, not the one I gave you).
Try adding it a Group by clause to it.
Change your query like this
SELECT all_kod, nev, megall, MIN(erkezik_ido) as erkezik_ido, MAX( indul_ido) as indul_ido
FROM menetrend, stat where statkod=all_kod and
train_selector in (SELECT train_selector
FROM service where vonatszam='910' and datum_ig>now() and
datum_tol<now() order by datum_ig desc, datum_tol desc)
FROM menetrend, stat where statkod=all_kod and
train_selector in (SELECT train_selector
FROM service where vonatszam='910' and datum_ig>now() and
datum_tol<now() order by datum_ig desc, datum_tol desc)
GROUP BY all_kod,nev,megall
order by
indul_ido
indul_ido
Try that out and see if it merges the duplicated records.
Is indul_ido the arrival time? Or departure time? My hungarian is very bad, I don't understand a single word :p
Best
Oliveiros
----- Original Message -----
From: "Ruzsinszky Attila" <ruzsinszky.attila@gmail.com>
To: "Oliveiros C," <oliveiros.cristina@marktest.pt>
Cc: "postgresql novice" <pgsql-novice@postgresql.org>
Sent: Thursday, September 03, 2009 12:44 PM
Subject: Re: [NOVICE] Modifying selected records
> 22;"5501289";"Győr";1;"07:39:00";"07:39:00";"910_22"
> 23;"5501289";"Győr";1;"07:41:00";"07:41:00";"910_23"
I'd like this:
<next count>; "5501289";"Győr"; "07:39:00"; "07:41:00"; "910_unknown"
> 28;"5544800";"Csorna";1;"08:00:00";"08:00:00";"910_28"
> 29;"5544800";"Csorna";1;"08:12:00";"08:12:00";"910_29"
For Csorna:
<next count>;"5544800";"Csorna";1;"08:00:00";"08:12:00";"910_unknown2"
_unknown means: it is a programmed selector, so not valid.
As you can see I want to merge the two time date in one record.
The problem comes from some specification misunderstood.
This timetable comes from three (almos) independent XML files.
> Indeed, I am not sure if I am understanding what you need.
Ihope you understand now.
> By duplicated record you mean records that have the first three columns the
> same ? E.G. ;"5544800";"Csorna";1; ?
Csorna and Győr is the problematic.
> And in these cases what do you need the train selector to be exactly?
I don't know exactly. I'm sure I have to change it because in the merged
record the sector is not valid.
> The left part of it (910) means what exactly?
vonatszam. In English: train number.
TIA,
Ruzsi
Hello Oliveiros, > I think That can be done by tweaking ur query a little (N.B: Your query, not > the one I gave you). Good news! > Change your query like this I'll test it soon. > Try that out and see if it merges the duplicated records. OK. > Is indul_ido the arrival time? Or departure time? My hungarian is very bad, > I don't understand a single word :p indul means departure erkezik means arrival Do you speak Hungarian? :-O I can translate the name of columns if you need but then I can more mistake when I put the query back in the needed form. So now the taskis clear for you? I've got very few experience in SQL so I need your help. Do you have any experience in XML, too? The source of the DB comes from XML files. Bye: Ruzsi