how to select last row on second CTE

mamatsiantar

use PARA2;
with pel(reg,inst,nama,alamat,sts)
as (select p.ID_Pelanggan
,p.No_Pelanggan
,p.Nama_Pelanggan
,p.Alamat
,case when p.Status_Pelanggan=2 then 'Aktip'
else 'Non Aktip'end'Status'
from PELANGGAN p),
ganti(reg,ml,nml,mb,nmb,thn)
as (select g.ID_Pelanggan
,g.Meter_Lama
,g.Nomor_Lama
,g.Meter_Baru
,g.Nomor_Baru
,g.Tanggal
from GANTI_Log g)
select p.reg
,p.inst
,p.nama
,p.alamat
,p.sts
,g.ml
,g.nml
,g.mb
,g.nmb
,g.thn
from pel p left outer join ganti g
on p.reg=g.reg
order by p.reg

Bernhard Hiller

What do you mean with "last row" - is it the row with newest date (g.Tanggal), and what would you like to select if no entry is present yet? What about

...
as (select TOP 1 g.ID_Pelanggan
,g.Meter_Lama
,g.Nomor_Lama
,g.Meter_Baru
,g.Nomor_Baru
,g.Tanggal
from GANTI_Log g
Order by g.Tanggal desc
)
...

Jorgen Andersson

Bernhards solution is valid for SQLServer. But what if you don't use SQL Server?

Wrong is evil and must be defeated. - Jeff Ello[^]

Lost User

If he is using HAL then he should have asked for HAL9000 compatible SQL :)

Bastard Programmer from Hell :suss: If you can't read my code, try converting it here[^]

Jorgen Andersson

"I'm sorry, Eddy. I'm afraid I can't do that."

Wrong is evil and must be defeated. - Jeff Ello[^]