[SOLVED] SQL Server 2012 how to retrieve ordinal position of specific record

SQL Server 2012 how to retrieve ordinal position of specific record

I have a table with following columns and sample data, where ItemID is unique:

   ID    User    ItemID     ExpiryDate  
    1    John    A13534     2015-12-24  
    3    Mark    B14532     2015-12-13
.......  
12415    John    B43245     2012-12-30
.......  
75741    John    C14542     2012-12-22
.......

Filters are:

  WHERE User = 'John' 
    AND ExpiryDate > getDate() 
  ORDER BY 
      ExpiryDate DESC

Considering that ItemID is unique, I need to find if itemID = B43245 is among first 1000 selected items or not

I tried to write solution both with Row_Number and RANK but I do not understand where to put the filter of ItemID :-(

Considering that the table contains more than 100.000 items, can you please suggest a solution that is not too heavy?

Thanks!

Solution

You can simply get the top 1000 records and then get your id. If you get a record, this id is within the top 1000. If you don't, it's not. Or you can change the outer select into count if you prefer.

select * from
(
    select top 1000 ID
    from yourtable
    WHERE User='John' AND ExpiryDate > getDate()
    ORDER BY ExpiryDate DESC
) x
where id = 'B43245'