Efficient SQL to retrieve latest record

[lilelvis2000]

hmmm... what about using a group by and having?

eg: select top 1 a_n_other_field from entries
group by entry_date, entry_number_for_date
having entry_date=MAX(entry_date) and entry_number_for_date=
MAX(entry_number_for_date)

?? bizarre I admit. But it could beat subqueries. (if it works)

[MrRobin]

Would it not be better to have a subquery bring the max (ie most recent) entry_date value in, and thus select the record with the same entry_date value

select a.* from entries a
where a.entry_date = (select max(b.entry_date) from entries b)
;

If there is an index on entry_date this should be efficient.

This is what I would do, except that you also need another where clause to filter for the highest entry number for that date, else it would bring back all the entried for the max date.

select *
from entries
where entry_date = (select max(entry_date) from entries)
and entry_number_for_date = (select max(entry_number_for_date) from
  entries where entry_date = (select max(entry_date) from entries))

I doubt this is the most efficient way though as you now have 2 sub queries and 1 subsub query. Could make it better by setting some variables to begin with?

[BrilloPad]

I seem to remember that sybase, with a clustered index, searches all entries where you specify equals. You could try "> yesterday" as that is more efficient.

I dont think there is best practice with sybase - I recently had a query that just would not use an index even when forced. There was a column logicallyDeleted which was usually "0" but was sometimes "1". It was not in the statistics. Specifying ="0" gives a 10% match - specifying !="1" gives 90%.

It really is just trial and error.

Somewhere I have a ppt "the day in the life of a query" - 170 pages long!!

[Ruprect]

Single index, clustered.

Fair point about the query plans, was just wondering if there was a renowned best practice method.

[BrilloPad]

I need to retrieve the latest record in a table thus:

Table: entries
Columns:
entry_date Date
entry_number_for_date int --increments with each entry for a given entry date
a_n_other_field char(10)

there is an index defined as entry_date, entry_number_for_date

Can anyone confirm whether this is the most efficient way of retrieve the latest record from this table please? Ta:


set rowcount 1
select * from entries
order by entry_date desc, entry_number_for_date desc
set rowcount 0

Is the index clustered? Is there only 1 index?

[DimPrawn]

Would it not be better to have a subquery bring the max (ie most recent) entry_date value in, and thus select the record with the same entry_date value

select a.* from entries a
where a.entry_date = (select max(b.entry_date) from entries b)
;

If there is an index on entry_date this should be efficient.

Possibly. What I tend to do when faced with two possible (reasonable) solutions is to implement both and then use the query plan feature of query analyser to compare the relative cost of both against real world data.

[MrMark]

Would it not be better to have a subquery bring the max (ie most recent) entry_date value in, and thus select the record with the same entry_date value

select a.* from entries a
where a.entry_date = (select max(b.entry_date) from entries b)
;

If there is an index on entry_date this should be efficient.

[Ruprect]

Thanks DP - but I don't think top 1 is available on Sybase? If you know different could you advise of the SQL?

Thanks,

Rup.

[DimPrawn]

I would suggest you use TOP 1 instead of setting rowcount (which is deprecated) and the can be optimised by the query optimiser where as rowcount cannot.

When you create the index, make sure it is a descending index, so that the data in the index is already sorted in the same way as your order by clause.

Do you do a lot of inserts? If so, having to maintain this index will slow down your inserts.

HTH

[Ruprect]

I need to retrieve the latest record in a table thus:

Table: entries
Columns:
entry_date Date
entry_number_for_date int --increments with each entry for a given entry date
a_n_other_field char(10)

there is an index defined as entry_date, entry_number_for_date

Can anyone confirm whether this is the most efficient way of retrieve the latest record from this table please? Ta:


set rowcount 1
select * from entries
order by entry_date desc, entry_number_for_date desc
set rowcount 0