Re: Modifying selected records - Mailing list pgsql-novice

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