Subject Re: [firebird-support] Re: group by ?? Fabian Chocron 2008-06-09T23:09:31Z
Dani,

What's the primary key?
Fabian

----- Original Message -----
From: "danyschaer" <danyschaer@...>
To: <firebird-support@yahoogroups.com>
Sent: Tuesday, June 10, 2008 1:45 AM
Subject: [firebird-support] Re: group by ??

Hi Fabian,

Thank you, but not, PROC and FECH are not unique.

Dany

--- In firebird-support@yahoogroups.com, "Fabian Chocron"
<fabianch@...> wrote:
>
> Assuming PROC + FECH is the primary key (unique key), you can:
>
> SELECT A.MOVI, A.PROC, A.DSCR, A.FECH, A.HORA, A.DSCR
> FROM MOVI A
> WHERE NOT EXISTS (SELECT * FROM MOVI B
> WHERE B.PROC = A.PROC
> AND B.FECH > A.FECH)
>
> Or, in a more efficient way:
>
> SELECT A.MOVI, A.PROC, A.DSCR, A.FECH, A.HORA, A.DSCR
> FROM MOVI A
> LEFT JOIN MOVI B ON (B.PROC = A.PROC AND B.FECH > A.FECH)
> WHERE B.PROC IS NULL
>
>
> Good luck
> Fabian
>
>
>
> ----- Original Message -----
> From: "danyschaer" <danyschaer@...>
> To: <firebird-support@yahoogroups.com>
> Sent: Tuesday, June 10, 2008 12:26 AM
> Subject: [firebird-support] group by ??
>
>
> Hi All;
>
> I have a table named MOVI with (as an example) this fields:
>
> MOVI Char(10)
> PROC Char(10)
> DSCR Char(10)
> FECH Char(8)
> HORA Char(4)
> DSCR Varchar(200)
>
> I have to obtain all the fields for each record that is the MAX
(FECH)
> from groups of PROC:
>
> select "PROC", max("FECH") from "MOVI" group by "PROC"
>
> Like this, but I have to get all the fields. How can I do that?.
>
> Thank you!
>
> Dany
>