CODEWITHSUNDEEP
t-sql
tqrecords
tqrecords

Reputation: 542

select MIN with order by

I have a @Temp table like this:

 Id    TypeId
--------------
14892 |   2
14893 |   3
--------------

I need to loop through one record at a time like this:

declare @Id int = (select MIN(Id) from @Temp)

while @Id IS NOT NULL
begin
    -- Code here
    select @Id = MIN(Id) from @Temp where @Id < Id
end

I also need to loop through the records with TypeId descending like this:

declare @Id int = (select MIN(Id) from @Temp order by TypeId desc)

So when looping through the records I should get 14893 then 14892 in that order. Of course I get this error when using order by:

 The ORDER BY clause is invalid in views, inline functions, derived tables, subqueries, and common table expressions, unless TOP, OFFSET or FOR XML is also specified.

Upvotes: 0

Views: 56

Answers (2)

tqrecords
tqrecords

Reputation: 542

I need to do a lot of complicated functions on each record. The result set will be minimal - maximum 10 records. I know looping should be avoided in tsql but sometimes its you're only option.

I ended up doing it this way:

while EXISTS (select Id from @Temp) 
begin 
    declare @Id int = (select top 1 Id from @Temp order by TypeId desc)

    -- Complicated functions

    delete from @Temp where Id = @Id
end

Upvotes: 0

paparazzo
paparazzo

Reputation: 45096

select ID, min(typeID) 
from @Temp  
group by ID

Upvotes: 1

Related Questions