last and/or first in a by group

Dino Vliet wrote on 16.05.2010 18:07:

Dear postgresql experts,

I want to know if postgresql has facilities for getting the first and or
the last in a by group.

Suppose I have the following table:

resnr,dep,arr,cls,dbd meaning reservationsnumber, departure station,
arrival station, the class of the reservation and the
daysbeforedeparture and records like:
xxx,NYC,BRA,C,80
xxx,NYC,BRA,M,75
xxx,NYC,BRA,Q,50
yyy,WAS,LIS,T,55
zzz,NYC,LIS,Z,40
zzz,NYC,LIS,J,39

I want to select only the most recent records being:
xxx,NYC,BRA,Q,50
yyy,WAS,LIS,T,55
zzz,NYC,LIS,J,39

Something like this?

SELECT *
FROM your_table t1
WHERE dbd = (SELECT min(dbd)
FROM your_table t2
WHERE t2.dep = t1.dep
AND t2.arr = t1.arr
AND t2.resnr = t1.resnr)

Regards
Thomas

Dino Vliet <dino_vliet@yahoo.com> wrote:

I want to know if postgresql has facilities for getting the first and or the last in a by group.

Suppose I have the following table:

resnr,dep,arr,cls,dbd meaning reservationsnumber, departure station, arrival station, the class of the reservation and the daysbeforedeparture and records like:
xxx,NYC,BRA,C,80
xxx,NYC,BRA,M,75
xxx,NYC,BRA,Q,50
yyy,WAS,LIS,T,55
zzz,NYC,LIS,Z,40
zzz,NYC,LIS,J,39

I want to select only the most recent records being:
xxx,NYC,BRA,Q,50
yyy,WAS,LIS,T,55
zzz,NYC,LIS,J,39

How would you accomplish this?

I googled and found this:
http://www.postgresonline.com/journal/index.php?/archives/68-More-Aggregate-Fun-Whos-on-First-and-Whos-on-Last.html

I hope there are alternatives because I don't have to program this myself. The other option is that I load this data in SAS and do it there but I only have SAS at work and want to do this at home.

You can either use window functions in PostgreSQL 8.4 (cf.
"FIRST_VALUE() OVER (...)"/"LAST_VALUE() OVER (...)") or use
the "DISTINCT ON" syntax:

| tim=# SELECT DISTINCT ON (resnr) resnr, dep, arr, cls, dbd FROM TestTable ORDER BY resnr, dbd;
| resnr | dep | arr | cls | dbd
| -------+-----+-----+-----+-----
| xxx | NYC | BRA | Q | 50
| yyy | WAS | LIS | T | 55
| zzz | NYC | LIS | J | 39
| (3 Zeilen)

| tim=#

Tim

From:
"Thomas Kellerer" <spam_eater@gmx.net>
To:
"" <pgsql-general@postgresql.org>Dino Vliet wrote on 16.05.2010 18:07:

Dear postgresql experts,

I want to know

if postgresql has facilities for getting the first and or

the

last in a by group.

Suppose I have the following table:

resnr,dep,arr,cls,dbd meaning reservationsnumber, departure station,
arrival station, the class of the reservation and the

daysbeforedeparture and records like:

xxx,NYC,BRA,C,80

xxx,NYC,BRA,M,75

xxx,NYC,BRA,Q,50
yyy,WAS,LIS,T,55
zzz,NYC,LIS,Z,40
zzz,NYC,LIS,J,39

I want to

select only the most recent records being:

xxx,NYC,BRA,Q,50
yyy,WAS,LIS,T,55
zzz,NYC,LIS,J,39

Something like this?

SELECT *
FROM your_table t1
WHERE dbd = (SELECT
min(dbd)
FROM your_table t2
WHERE
t2.dep = t1.dep
AND t2.arr = t1.arr
AND t2.resnr = t1.resnr)

Regards
Thomas

****************

Thanks for your answer and if I look at it from a functionality point of view, this does the trick.

However, my table t1 (and hence t2) contains 6 million records AND I'm planning to do this repeatedly (with a scripting language for various moments in time) so this will end up being a very slow solution.

How can I speed these kind of queries up? By using indices, but on what columns would that be the best way then?
Or by trying to do this one time by constructing a table with the relevant information which can be used in such a way that I join thing in stead of using this subquery construction.

Thanks
Dino

Hi,
I'm trying to update postgresql ver 8.7.3 to 8.4.4
At the end of the installation when trying to styart the database server.... I get the following error meassage: "Problem running post-install step. Installation may not complete correctly. Failed to start the database server"

And when I try to start the server manually I get the following console print out:
Start DoCmd(net postgresql-8.4)...
System error 1069 has occured.
The service did not start due to a logon failure.
Failed to start the database server.

I've checked the 8.4\Data folder and it is empty.

Is ther anyone who has a solution for this?

Kind regards,
Paul

On 5/25/10 3:08 PM, Malm Paul wrote:

Hi,
I'm trying to update postgresql ver 8.7.3 to 8.4.4

The One-Click installer will not upgrade 8.3.7 to 8.4.4 rather it will
create a parallel 8.4.4 installation as both have different major versions.

At the end of the installation when trying to styart the database
server.... I get the following error meassage: "Problem running
post-install step. Installation may not complete correctly. Failed to
start the database server"
And when I try to start the server manually I get the following
console print out:
Start DoCmd(net postgresql-8.4)...
System error 1069 has occured.
The service did not start due to a logon failure.
Failed to start the database server.

Please attach %TEMP%\install-postgresql.log, which will help to analyze
things more clearly.

I've checked the 8.4\Data folder and it is empty.
Is ther anyone who has a solution for this?
Kind regards,
Paul

--
Regards,
Sachin Srivastava
EnterpriseDB <http://www.enterprisedb.com&gt;, the Enterprise Postgres
<http://www.enterprisedb.com&gt; company.

On 25 May 2010, at 11:38, Malm Paul wrote:

Hi,
I'm trying to update postgresql ver 8.7.3 to 8.4.4

I know it's totally unrelated, but when did it become popular to send (HTML) messages in a very small blue font? I find this rather hard to read and usually fall back to the plain text alternative (which is included, thankfully).

This is not quite the first message formatted like this, I merely thought I should finally just ask. I did notice that most of the people sending messages formatted like this one don't appear to be native speakers (of English), does that have anything to do with it?

Alban Hertroys

--
Screwing up is an excellent way to attach something to the ceiling.

!DSPAM:737,4bfba33510414354318240!

On Tue, May 25, 2010 at 11:15 AM, Alban Hertroys
<dalroi@solfertje.student.utwente.nl> wrote:

On 25 May 2010, at 11:38, Malm Paul wrote:

Hi,
I'm trying to update postgresql ver 8.7.3 to 8.4.4

I know it's totally unrelated, but when did it become popular to send (HTML) messages in a very small blue font? I find this rather hard to read and usually fall back to the plain text alternative (which is included, thankfully).

ask microsoft folks working on outlook. Somehow they thought it is a good idea.

--
GJ

On Tuesday 25. May 2010 12.15.14 Alban Hertroys wrote:

I know it's totally unrelated, but when did it become popular to send (HTML)

messages in a very small blue font? I find this rather hard to read and usually
fall back to the plain text alternative (which is included, thankfully).

At least there's a plain text fallback. Messages in HTML only are totally
unreadable in a plaintext MUA, and should IMO be bounced from the list.

regards,
--
Leif Biberg Kristensen
http://solumslekt.org/blog/

-----BEGIN PGP SIGNED MESSAGE-----
Hash: RIPEMD160

At least there's a plain text fallback. Messages in HTML only
are totally unreadable in a plaintext MUA, and should IMO be
bounced from the list.

Not totally unreadable. Good MUAs find ways to handle it. For
example, my mutt[1]Technically, mutt and mailcap is more than happy to automatically pipe
things through lynx -dump which works pretty well. That said,
+1 to bouncing email with no text at all.

[1]: Technically, mutt and mailcap

- --
Greg Sabino Mullane greg@turnstep.com
End Point Corporation http://www.endpoint.com/
PGP Key: 0x14964AC8 201005251117
http://biglumber.com/x/web?pk=2529DF6AB8F79407E94445B4BC9B906714964AC8
-----BEGIN PGP SIGNATURE-----

iEYEAREDAAYFAkv76icACgkQvJuQZxSWSsjtOACgxd0BRnE73BZJ1w1zfpKZ946s
x34Ani5IKIpCMzU/+Xh3nB1+U47Q1tLX
=gc5X
-----END PGP SIGNATURE-----