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
|