2

Select Last(column_name) from table_name will return the last value in the column in standard SQL. What is a similar query that will work in derby to fetch the last value in the column?

Please find the sample table below: 'secondcol' is the column name in the table.

          secondcol
          33
          45
          78

Select Last(secondcol) from table_name in sql will return 78 as output. If i want to get similar output in javadb/derby database, how can i query it? I don't want to change any order in the column values.

matts
  • 6,738
  • 1
  • 33
  • 50
crazyvi
  • 57
  • 1
  • 1
  • 11
  • 3
    `SELECT secondCol FROM tableName ORDER BY id DESC LIMIT 1` maybe? – Joop Eggen Feb 28 '13 at 15:10
  • Define the last value? Do you mean the most recently inserted value? The LAST() function is not supported by many database engines. – RudolphEst Feb 28 '13 at 15:19
  • Yes I mean the most recently inserted value and I want to get it without the need of unique key values like primary key column. – crazyvi Mar 01 '13 at 08:23

2 Answers2

4

To select last row in table is little bit different then it is in pure SQL:

SQL:

SELECT * FROM tableName ORDER BY id DESC LIMIT 1;

DERBY:

SELECT * FROM tablename ORDER BY id DESC FETCH FIRST ROW ONLY;

Have a nice day ;)

Yurdaaaaa
  • 89
  • 3
  • 11
1

Is there some unique key on the table where you could form the question as "give me the secondcol value on the row with the max key value"? If so, there is a technique that will work in any database engine - the idea is to concatenate the key plus any desired result data, perform a min/max, then extract the result data.
See here and here.

Community
  • 1
  • 1
bwperrin
  • 680
  • 5
  • 12
  • I want most recently inserted value in the column without depending on unique key on the table. – crazyvi Mar 01 '13 at 08:25
  • To get the most-recently-inserted row, add a generated column with an auto-incrementing counter in it. Then select the row with the MAX value of that column. – Bryan Pendleton Mar 01 '13 at 14:48