statement question

[nimbus123]

One table, 4 columns: index, date, a and b.
Let's say I have the folling rows:

0, 20051201, 4, 1
1, 20051202, 4, 1
2, 20051203, 4, 1
3, 20051204, 4, 1
4, 20051203, 3, 2
5, 20051204, 3, 2
6, 20051208, 3, 2
7, 20051204, 2, 1
8, 20051207, 2, 2

I need to delete the oldest rows which have the same a and b - and leave the 2 newest rows untouched. So a delete-statement should remove row 0, 1 and 4. I'm using postgres. Any idea?
Thanks in advance!

 

[JarlH]

Something like:

DELETE FROM sometable
WHERE index IN
(SELECT index FROM sometable t0
WHERE index <
(SELECT MAX(index)
FROM sometable t1
WHERE index <
(SELECT MAX(index)
FROM sometable t2
WHERE t1.a = t2.a AND t1.b = t2.b)
AND t0.a = t1.a AND t0.b = t1.b))


Note that DATE is a reserved word in ANSI SQL, consider renaming that column to avoid future problems. (And some DBMS products also have INDEX as a reserved word.)

 

[dwight1]

Hi Guys,

my question is similar to the one posted here and hence i am posting it as a sub thread.

I have a case statement
SELECT NumAcctsMo, MonthNo, Year, ACTYPE, BRANCH, Name, CASE WHEN MonthNo = 1 THEN Numacctsmo END AS Jan,
CASE WHEN MonthNo = 2 THEN Numacctsmo END AS Feb FROM dbo.DDAcctsMonthly
this would give me the result as follows.

Branch Name Jan Feb Dec
1 ABC 200
1 BCD 300
1 XYZ 1200
2 ABC 200
2 BCD 300
2 XYZ 1200
Can any one please advise how to get it in the following format.
Branch Name Jan Feb Dec
1 ABC 200 300 1200
2 BCD 200 300 1200


Thanks in advance.

Dwight

 

[dnoeth]

Hi Dwight,
IMHO it's totally different

But nevertheless:

You simply have to do a Group By:

SELECT
NumAcctsMo, MonthNo, Year, ACTYPE, BRANCH, Name,
max(CASE WHEN MonthNo = 1 THEN Numacctsmo END) AS Jan,
max(CASE WHEN MonthNo = 2 THEN Numacctsmo END AS Feb
FROM dbo.DDAcctsMonthly
group by NumAcctsMo, MonthNo, Year, ACTYPE, BRANCH, Name

Dieter