On 01/05/2015 04:50, Bob M wrote:
Hi
I have a single database table where the primary key is (col1 =Date,
col2=Time) say
This works fine when I wish to delete the oldest record for instance
However I have another column say col3 which holds an increasing sequencial
number (i.e. 1, 2,3 etc but not every record has an entry in that column -
many records have nothing in that column
By which you mean NULL?
I am wishing to retrieve the latest two records where that column has a
number in it
This should do the trick:
SELECT * FROM mytable WHERE col3 IS NOT NULL
ORDER BY col1 DESC, col2 DESC
FETCH 2 ROWS ONLY
There are probably other ways to do it too... for example, since col3 is
increasing,
SELECT * FROM mytable ORDER BY col3 DESC NULLS LAST
FETCH 2 ROWS ONLY
...which I'd guess would probably be marginally faster, if col3 were indexed.
HTH,
--
John English
