SELECT RECORD FROM ORACLE DATABASE

I want to extract the "last" inserted record from database, using this type of query

SELECT A.IDOC_NUM, A.SEG_NUM
FROM  AGR3PL_LINE A, AGR3PL_IDOC B
WHERE A.IDOC_NUM(+) = B.IDOC_NUM
AND B.IDOC_NUM = '457154' AND B.CREATION_DATE = (SELECT MAX(B.CREATION_DATE) FROM AGR3PL_IDOC B)
ORDER BY A.SEG_NUM DESC;

The result is this:

IDOC_NUM SEG_NUM
457154     9
457154     8
457154     7
457154     6
457154     5
457154     4
457154     3
457154     2
457154     10
457154     1

This is correct, but the entry 10 is last entered, but as you can see the format result is not good, it sholud be 10,9,8..., is it maybe problem with datebase or somethig else???

Answers


ORDER BY TO_NUMBER(A.SEG_NUM) DESC;

Well, SEG_NUM is stored as a "string" type (VARCHAR2 or anohter).

Set the column type as NUMBER, and sort will be fine.


Probably SEG_NUM is text type (e.g. VARCHAR), so it is not sorted like a number.

You can change type of this column (it is the best solution, because if it is number it should have numeric type) or you can convert this column type during execution (it can reduce performance):

SELECT A.IDOC_NUM, A.SEG_NUM 
FROM  AGR3PL_LINE A, AGR3PL_IDOC B 
WHERE A.IDOC_NUM(+) = B.IDOC_NUM 
AND B.IDOC_NUM = '457154' AND B.CREATION_DATE = (SELECT MAX(B.CREATION_DATE) FROM AGR3PL_IDOC B) 
ORDER BY TO_NUMBER(A.SEG_NUM) DESC;

the problem seem to be SEG_NUM is varchar in ur case. So it is treating 10 lower than 2.

Do it this way -

ORDER BY to_number(A.SEG_NUM) DESC

Need Your Help

Lazy for comprehension evaluation in scala

scala functional-programming immutability lazy-evaluation for-comprehension

I'm new to scala and most functional languages and I'm currently trying to factor a number. i've written the code: