how to change a value in the last row returned by an sql query?

I'm having trouble writing an SQL query that returns rows for which a specific value is put in a field of the last returned row (this modification is only made on the results returned, there must be no modification of the database records).

Here is an example :

SELECT A.a, 
       A.b, 
       A.c
  FROM A
 WHERE A.b = 10

Results returned :

A.a |A.b |A.c
--------------
1    10   zaza
2    10   zozo
3    10   zuzu
4    10   zozo

I would like 开发者_如何学JAVAthe request to automatically put a specific value in the A.c field of the last row, such as :

A.a |A.b |A.c
--------------
1    10   zaza
2    10   zozo
3    10   zuzu
4    10   XXXX

---

If the order of the rows are determined by column a, the following would replace the C-value in the "last" row.

select a
      ,b
      ,case when row_number() over(order by a) = count(*) over() then 'XXX' 
                                                                 else c end as c
  from v
order by a;   

---

Firstly you need an ORDER BY statement. Oracle does not guarantee that rows will be returned in the order that they were entered, so you need to change your SQL to:

select A.a, A.b, A.c
From A
Where A.b = 10
ORDER BY A.a;

for the concept of a "last" row to have any meaning.

Here's one way... there may well be a better way of doing it than this...

SELECT A, B, C
FROM (
SELECT A.A, A.B, A.C
FROM   A
WHERE  A.A <> (SELECT MAX(A.A) FROM A WHERE A.B = 10)
AND    A.B = 10 
UNION ALL
SELECT A.A, A.B, 'whatever'
FROM   A
WHERE  A.A = (SELECT MAX(A.A) FROM A WHERE A.B = 10)
AND    A.B = 10
)
ORDER BY A.A

You don't say what the behaviour should be if there's 2 rows where A.A are the same (if this is possible?)

---

A bit less messy:

SELECT A,
       B,
       CASE
         WHEN A = (SELECT MAX(A) FROM TEST WHERE B = 10) THEN 'XXXX'
         ELSE C
       END C
FROM TEST