Обсуждение: 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,

> 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