Find MAX number without using MAX,Where Clause, Order by


How can some one retrieve the max of the number from a table using TSQL where you are not allowed to use the MAX function or the order by clause or the Where clause.

I have found out a solution below, although the algorithm can be changed by using different sort techniques. I found this is simple to understand so the users might build upon this

Method 1

create table salaries (empid int identity(1,1), salary int)
go
insert into salaries (salary) values (1000)
go
insert into salaries (salary) values (2000)
go
insert into salaries (salary) values (3000)
go
insert into salaries (salary) values (2400)
go
insert into salaries (salary) values (100)
go
insert into salaries (salary) values (200)
go
insert into salaries (salary) values (1050)
go
insert into salaries (salary) values (8000)
go
insert into salaries (salary) values (1000)
go
insert into salaries (salary) values (2000)
go
insert into salaries (salary) values (1000)
go
insert into salaries (salary) values (2000)
go

select top 1 s1.salary from salaries s1
      inner join salaries s2 on s1.salary> s2.salary
      and s1.empid not in (select s2.empid from salaries s1
      inner join salaries s2 on s1.salary> s2.salary)

Method 2

create table #table
(
	num int
)

insert into #table values (1)
insert into #table values (2)
insert into #table values (3)
insert into #table values (4)
insert into #table values (10)
insert into #table values (6)
insert into #table values (7)
insert into #table values (8)
insert into #table values (9)

declare @max_number int
declare @present_num int
declare @previous_num int

SET @present_num = 0
SET @previous_num = 0
SET @max_number = 0
update t
SET	@present_num = t.num
    ,@max_number = case when @present_num > @max_number Then @present_num  else @max_number end
    ,@previous_num = t.num
FROM #table t

select @present_num ,@max_number,@previous_num
Advertisements

Leave a Reply

Fill in your details below or click an icon to log in:

WordPress.com Logo

You are commenting using your WordPress.com account. Log Out / Change )

Twitter picture

You are commenting using your Twitter account. Log Out / Change )

Facebook photo

You are commenting using your Facebook account. Log Out / Change )

Google+ photo

You are commenting using your Google+ account. Log Out / Change )

Connecting to %s