Better way to write this SQL

Question

I have the following table:

create table ARDebitDetail(ID_ARDebitDetail int identity, 
                  ID_Hearing int, ID_AdvancedRatePlan int)

I am trying to get the latest ID_AdvancedRatePlan based on a ID_Hearing. By latest I mean with the largest ID_ARDebitDetail. I have this query and it works fine.

    select ID_AdvancedRatePlan
    from ARDebitDetails
    where ID_Hearing = 135878
    and ID_ARDebitDetail = 
            (   select max(ID_ARDebitDetail) 
                from ARDebitDetails 
                where ID_AdvancedRatePlan > 0 and ID_Hearing = 135878
            )

However, it just looks ugly and smells bad. Is there a way to rewrite it in a more concise manner?

Answer 1

SELECT TOP 1 ID_AdvancedRatePlan
FROM ARDebitDetails
WHERE ID_Hearing = 135878
AND ID_AdvancedRatePlan > 0
ORDER BY ID_ARDebitDetail DESC

Answer 2

In RDBMS the code smell is rarely in the SQL text, the code smell in in your schema. Case in point: search for some condition in a table without proper index, which will always result in a table scan. To get the last ID_AdvancedRatePlan for a given ID_Hearing, organize the table accordingly:

create clustered index cdxARDebitDetail 
on ARDebitDetail (ID_Hearing, ID_ARDebitDetail DESC);

If changing the clustered index is undesirable, for various resons, a covering nonclustered index should be provided. Whatever you do, the gist of it is that you should not scan the table each time, that's the recipe for disaster at deployment.

Then you can search as you wish, and Electric's answer is a very good answer.

Answer 3

Another solution using a common table expression would be:

With RankedItems
    (
    Select ID_ARDebitDetail, ID_AdvancedRatePlan
        , ROW_NUMBER() OVER( ORDER BY ID_ARDebitDetail DESC) As ItemRank
    From ARDebitDetails 
    Where ID_AdvancedRatePlan > 0
        And ID_Hearing = 135878
    )
Select ID_AdvancedRatePlan
From RankedItems 
Where ItemRank = 1