db-derby-user mailing list archives

Site index · List index
Message view « Date » · « Thread »
Top « Date » · « Thread »
From John English <john.fore...@gmail.com>
Subject Re: retrieving records based on a single column contents
Date Fri, 01 May 2015 11:24:54 GMT
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

Mime
View raw message