SQL subquery to determine whether or not a particular row is the first item that matches the subquery

Question

Here is my VB.NET and SQL query (which is one of the things I'm particularly interested in having critiqued):

Private Sub BindHistory()

    Dim list = New List(Of CSRate)

    ' I hate the "case when" and subquery here - it seems like there should
    ' be a better way to do this
    Const Query As String =
        "select n.PayRate,
                n.CreatedOn,
                n.CreatedByUserName,
                (case when exists(select top 1 m.BusinessUnitID
                                  from TrackRates m
                                  where m.RateID = n.RateID AND
                                        m.TrackRateID < n.TrackRateID)
                 then 'entered rate of' else 'edited rate to' end) as Verb
        from    Rates, TrackRates n
        where   Rates.RateID = n.RateID AND
                Rates.ClientID = @id"
    Using reader = DBAccess.ExecuteReader(User.ActiveBusinessUnitID, CommandType.Text, Query, New SqlParameter("@id", SessionState.Client.ID))
        If reader.HasRows Then
            While reader.Read()
                Dim rate = New CSRate()
                rate.ActionDate = reader.Item("PayRate")
                rate.Action = $"User {reader.Item("Verb")} {reader.Item("PayRate")}"
                rate.UserID = reader.Item("CreatedByUserName")

                list.Add(rate)
            End While
        End If
    End Using

    CSHistory.DataSource = list
    CSHistory.DataBind()
End Sub

For the record, yes, I do realize that I should probably move the query to a stored procedure rather than having it "directly" in code-behind like this. Also, n and m are probably terrible names that I should refactor.

What I'm trying to accomplish with this SQL query is to determine whether a particular item is the first row that matches the criteria in the where clause (the text I have to display on the front end will differ). For example:

| TrackRateID | Other columns |
| 1           | ...           |
| 2           | ...           |
| 3           | ...           |

In this case, 2 and 3 should have the verb edited rate to (because there's a TrackRateID that's less than their TrackRateID) and 1 should have the verb entered rate of. (There is, of course, no guarantee that the "first" row will actually have an ID of 1).

Here's my ASP.NET front-end code:

<div id="clientHistoryPopup" style="display:none">
        <asp:GridView runat="server" ID="CSHistory" AutoGenerateColumns="false">
            <Columns>
                <asp:BoundField DataField="UserID" HeaderText="User ID" />
                <asp:BoundField DataField="ActionDate" HeaderText="Date" />
                <asp:BoundField DataField="Action" HeaderText="Pay Rate History" />
            </Columns>
        </asp:GridView>
    </div>

The case when and subquery particularly irk me. If I'm reading this properly (and please correct me if I'm wrong because I'm not too confident about this point), this would make the whole thing O(n log n).

Also, it seems like that makes the database logic too tightly "coupled" to my front-end logic, which strikes me as architecturally inappropriate.

Am I correct about the computational complexity? Is there a better way to do this that has a lower computational complexity and is less "coupled" to the front end?

Answer 1

There is no natural order. With no order by the order of the rows is not guaranteed.

What you are looking for is not totally clear to me. But I think this is what you are looking for. Pretty sure window function is what you are looking and it is efficient.

  select tt.AuctionID, tt.StartingPrice
       , tt.rn 
       , case when rn = 1 then 'first' else 'not first' end as [verb]
  from ( SELECT [AuctionID]
              , [Price]
              , ROW_NUMBER() over (partition [AuctionID] by order by [Price]) as rn
         FROM [Test].[dbo].[Auction]
       ) tt

select *
     , case when rn = 1 then 'entered rate of' else 'edited rate to' end as [verb]
from  ( select n.PayRate, n.CreatedOn, n.CreatedByUserName
            ,  row_number() over (partiton by RateID order by TrackRateID) as rn
          from Rates, TrackRates n
         where ClientID = @id 
      ) tt
 order by RateID, TrackRateID