Thread: 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
andthe 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
inSAS 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

DIV {MARGIN: 0px }

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 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
thisrather 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
mostof the people sending messages formatted like this one don't appear to be native speakers (of English), does that
haveanything 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
findthis 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] 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-----