1

Suppose my table structure as follows:

id | Word
---|-----
1  | a
2  | aa
.  | ..

I have a list of id's like this:

(...,900, 1000, 2000, 3000, 4000,....)

I want to find the biggest id less than each id in the above list. My table id's is not necessarily consecutive and there are some gaps between two successive id's, for example:

(...,889,900,950,952,997,1000,1001,1010,1920,2000,2990,3000,3500,4000,...)

The expected result according to the above list would be:

(889, 997, 1920, 2990, 3500,...)

How do i achieve desired results?

Rezaeimh7
  • 1,467
  • 2
  • 23
  • 40

3 Answers3

2

Use a common table expression and ROW_NUMBER()

;WITH cte AS(
    SELECT *, ROW_NUMBER() OVER (ORDER BY ID) rowNum
    FROM example)

SELECT ID, word
FROM cte
WHERE rowNum IN (
    SELECT (rowNum - 1)
    FROM cte
    WHERE ID IN ('900','1000','2000','3000','4000'))
    --WHERE ID IN (SELECT ID FROM <tableWithIDs>))

enter image description here

If you already have all of the ID you are looking for in another table, you would instead use the commented portion of my answer instead of the hardcoded IN list.

This will work only if the ID you are looking for exists in the table. So, as noted in a comment below if you were searching for 1001 you would not get 997, unless 1001 existed in the table (meaning, if it existed it would get a rowNum value and could be used to decrement in the subquery)

[DEMO HERE]

The following is another way to just see what the previous ID is for each row:

SELECT *, LEAD(ID,1) OVER(ORDER BY ID DESC) PreviousID
FROM example
ORDER BY ID

enter image description here

Brien Foss
  • 3,336
  • 3
  • 21
  • 31
  • My id list is far bigger than list appeared in the question body. – Rezaeimh7 Mar 11 '18 at 06:05
  • @m.r226 ok, give me a few minutes and I'll give you a more robust method – Brien Foss Mar 11 '18 at 06:05
  • @m.r226 - I have posted a more robust/dynamic approach to solving this problem. – Brien Foss Mar 11 '18 at 06:24
  • What if you change `1000` to `1001` in your WHERE? – dnoeth Mar 11 '18 at 11:20
  • @dnoeth - I hear you, `997` won't show up because `1001` doesn't exist in my test table. I've added another way for the OP to see the data that they might be looking for. Based on my testing, seems Gordon's answer works without the `ID` needing to exist – Brien Foss Mar 11 '18 at 13:34
1

I would simply do:

select v.val, t.*
from (values (900), (1000), (2000), (3000), (4000) ) v(val) outer apply
     (select top 1 t.*
      from t
      where t.id < v.val
      order by t.id desc
     ) t;

This allows you to see the value on each of the rows. That is probably important because SQL result sets are unordered and it will not be obvious which value goes with which row.

EDIT:

If you know the row numbers are in the table, the most performance solution is probably:

select t.*
from (select t.*, lead(id) over (order by id) as next_id
      from t
     ) t
where next_id in ( . . . );
Gordon Linoff
  • 1,242,037
  • 58
  • 646
  • 786
  • 1
    @GordonLinoff- Your answer doesn't require the seeking `ID` to be in the table, which really makes your answer the most applicable solution to future visitors (in the event their `ID` was not in the table). _"I would simply do:..."_ makes me laugh a bit though.. I've got goals man! – Brien Foss Mar 11 '18 at 14:34
  • 1
    @Brien . . . Your comment made me realize that there is a more performant solution if you know the ids are in the table. – Gordon Linoff Mar 11 '18 at 14:38
  • @GordonLinoff- Cool! Yeah I was a little apprehensive to mention `LEAD()` albeit in a different context in my edit earlier because I've been bitten by the hell fire from other users or even the OP when I find out they aren't using SQL Server 2012+. – Brien Foss Mar 11 '18 at 14:42
0

This should work and I think it will be fairly efficient. 

declare @V table (num int primary key);
insert into @V values (800), (889), (900), (997), (1000), (1910), (1920), (2000), (2990), (3000), (3500), (4000);
declare @T table (num int primary key);
insert into @T values (800), (900), (1000), (1200), (2000), (3000), (4000);
select tt.vP 
  from ( select t.num as t, v.num as v
              , LAG(v.num) over (order by v.num) as vP
           from @V v
           left join  @T t
             on v.num = t.num 
       ) tt 
 where tt.t  is not null 
   and tt.vP is not null
 order by tt.vP

Not clear how you want it to behave 

select t.num 
     , (select max(v.num) from @V v where v.num < t.num) as prior
from @T t
paparazzo
  • 44,497
  • 23
  • 105
  • 176