Return Newest records first

Question

Post reply

Return Newest records first

Ray Waldron

Old Hand

Points: 353
More actions
August 3, 2005 at 4:53 am

#86983

Hi all,
I have a linked server(sbase_live_link) from SQL 2000 to Pervasive Btrieve.
That conatins 130,000 records

The following statement works ok.
select
top 100 *
From
Openquery(sbase_live_link, 'SELECT Ref_Number,Convert(date_booked, SQL_VARCHAR)as Date_Booked from jobs ')order by date_booked desc
I needed to convert date_booked to SQL_VARCHAR or my query would fall over when it meets the first Null in the pervasive database.
When I run the statement SQL runs for 20 Mins ( Checking all 130,000 records ) Mins before returning the first 100 rows .
eg:
Ref    Date_Booked
35    2002-02-21
40    2002-02-21
41    2002-02-21
However , the dates returned are the oldest Dates 2002.
Where as I wish to retun the top 100 most recent dates 2005
Does anyone now the best syntax to use in order to return the newest records first. Sql by default seems to find the oldest records first. is there a way to change this ?
I have also tried the following to retun only the most recent 3 months from (current date - 90) but the syntax is wrong .
select top 100 *
From
Openquery(sbase_live_link, 'SELECT Ref_Number,date_booked from jobs ')where date_booked > now -90 order by date_booked desc

Thx for any help,
Ray

Viewing 7 posts - 1 through 6 (of 6 total)

You must be logged in to reply to this topic. Login to reply

Calli Pieterse-250080 SSC Rookie Points: 34 More actions · Answer 1

Try putting the "order by" clause inside the inner query.

And there's no "Now" in SQL, try GetDate()

Paul Cresham Hall of Fame Points: 3623 More actions · Answer 2

Calli's solution should work. If I understand correctly, the way you are doing it now, it will return the first 100 results from the passthrough query without ordering, then order these results.

With the order by inside the passthrough query, it will return the first 100 of the ordered results, rather than the first 100 unordered results then meaninglessly sort them.

Ray Waldron Old Hand Points: 353 More actions · Answer 3

Thx For the Reply Guys that worked !! I'm Nearly there.

I also need to use a 'where' so That I can select the Last 3 Months of records eg: where date_booked >= GetDate() -90

but I can only get this to work if I put the where outside the brackets and it takes 20 Mins

select top 100 *

From Openquery(sbase_live_link, 'SELECT Ref_Number,date_booked from jobs order by date_booked desc')

Runs in 2 secs!!

Now if I try

select *

From Openquery(sbase_live_link, 'SELECT Ref_Number,date_booked from jobs where date_booked >= GetDate() -90 order by date_booked desc')

This fails with Invalid scalar function : GetDate.

Which I presume means Pervasive doen't recognise the syntax getdate()

The statement

From Openquery(sbase_live_link, 'SELECT Ref_Number,date_booked from jobs order by date_booked desc') where date_booked >= GetDate() -90

Does come back after 20 Mins ok but as this is outside the openquery there is wastage..

Do you know the syntax to get tis to work ??

Thx

Ray

Paul Cresham Hall of Fame Points: 3623 More actions · Answer 4

Should work in the linked server's syntax (might also depend on the driver being used) if you put the WHERE clause inside the passthrough query too.

I do it with Progress and get the same (i.e. if you put the WHERE outside it returns all rows to SQL Server which then performs the WHERE, but inside it's all done on the remote server so hence the massive increase in speed). With the Progress driver I use it's just standard SQL-92 syntax. I think you might need to use the SYSDATE function instead of GETDATE() but not sure if this is still platform dependent.

Ray Waldron Old Hand Points: 353 More actions · Answer 5

Hiya Guys thx for the help..

I have the following working.. The syntax for btrieve current date is curdate()

In Btrieve itself if I run the following it comes back with results immediately

select * from jobs order by date_booked desc

In SQL Linked server if I run the same Distributed Query it comes back very quickly

select * from openquery(sbase_live-link,'select * from jobs order by date_booked desc ')

Because, as you huys said , the orderby is now within the openquery that gets sent to btrieve. This speeds things up alot

However In Btrieve again if I run

select * from jobs where date_booked = curdate() order by date_booked desc

I get all todays records back very quickly

The same Distributed query in SQL takes 20Mins even though the where is inside the openquery ??

select * from openquery(sbase_live-link,'select * from jobs where date_booked = curdate() order by date_booked desc ')

Any more help??

if I run select top 12000 * from openquery(sbase_live-link,'select * from jobs order by date_booked desc ')

it comes back quickly with roughly 3 months worth of data but I was hoping to use curdate()-90 to be more accurate

Ray

Michael Du Bois SSCertifiable Points: 5143 More actions · Answer 6

Ray have you tried computing the date outside of the query

select * from openquery(sbase_live-link,'select * from jobs where date_booked > '5/5/2005' order by date_booked desc ')

Mike