How to find the maximum possible value of a given datatype

Does anyone know of a built-in function to return the maximum possible
value of a given datatype? I have to return the biggest value for a
smalldatetime or datetime in a view if the field is null, but can't
find such a function. The closest I've come is:

select datalength(cast(getdate() as smalldatetime))

...but that only return the number of bytes, not the value itself,
which is '6-6-2079 11:59'

I know I could create my own lookup table and function, but I was
hoping that Transact-SQL would have a built-in solution

--John Hunter>One alternative is to write your own function that encapsulates the

Quote:

Originally Posted by

>hardcode. Pass the table and column names, query the system tables
>for the type and related information such as max length of a varchar,
>and then return it. Clumsy, no doubt.

My apologies, it is nowhere near as simple as I described as, at a
minimum, one function for each datatype group (dates, character
strings, etc) would be required.

Roy Harvey
Beacon Falls, CT|||jshunter@.waikato.ac.nz wrote:

Quote:

Originally Posted by

Does anyone know of a built-in function to return the maximum possible
value of a given datatype? I have to return the biggest value for a
smalldatetime or datetime in a view if the field is null,

If you (and the recipient of this return value, if it isn't another
piece of T-SQL) can wrap your head around ternary logic, then you
can use the null value as is:

where not (some_date end_date)

Or, depending on the expected longevity of the system, you can (a)
hard-code June 6, 2079 (max smalldatetime) or (b) avoid smalldatetime
and hard-code December 31, 9999 (max datetime). I would personally
go with (b), as I can't imagine actually designing a system that had
a good reason to use smalldatetime for anything.|||No there isn't.

But there are solutions. You can read more at
http://groups.google.nl/group/micro...743f4aea485c6d1
(url may wrap)

HTH,
Gert-Jan

"jshunter@.waikato.ac.nz" wrote:

Quote:

Originally Posted by

>
Does anyone know of a built-in function to return the maximum possible
value of a given datatype? I have to return the biggest value for a
smalldatetime or datetime in a view if the field is null, but can't
find such a function. The closest I've come is:
>
select datalength(cast(getdate() as smalldatetime))
>
...but that only return the number of bytes, not the value itself,
which is '6-6-2079 11:59'
>
I know I could create my own lookup table and function, but I was
hoping that Transact-SQL would have a built-in solution
>
--John Hunter

How to find 1st Maximum & 2nd maximum No. in a table?

Hi

I have table like this

State Version

CA 19

CA 8

CA 15

CA 6

MA 12

MA 7

MA 10

MA 5

Like this I have 50 states with different Version No.s (Version No.s are unique)

I want to display output like this(in SQL Server 2000)

State 1^st Maximum 2^nd Maximum

CA 19 15

MA 12 10

Please advice

Thanks

Here you go..

Code Snippet

Create Table #data (

[State] varchar(3) ,

[Version] int

);

Insert Into #data Values('CA','19');

Insert Into #data Values('CA','8');

Insert Into #data Values('CA','15');

Insert Into #data Values('CA','6');

Insert Into #data Values('MA','12');

Insert Into #data Values('MA','7');

Insert Into #data Values('MA','10');

Insert Into #data Values('MA','5');

Code Snippet

--Only on SQL Server 2005

;With CTE

as

(

Select *, Row_number() Over(Partition By [State] Order By [Version] Desc) RowId From #data

)

Select

[State]

,Max(Case When RowId=1 Then [Version] End) [1st Max]

,Max(Case When RowId=2 Then [Version] End) [2nd Max]

From

CTE

Group By

[State]

Code Snippet

--or

--Only on SQL Server 2005

;With CTE

as

(

Select *, Row_number() Over(Partition By [State] Order By [Version] Desc) RowId From #data

)

Select

[State]

,[1] as [1st Max]

,[2] as [2nd Max]

From

CTE

Pivot

(

Max([Version])

For RowId in ([1],[2])

) as PVT

Code Snippet

--Any SQL Server version

Select

[State]

,Max(Case When RowId=1 Then [Version] End) [1st Max]

,Max(Case When RowId=2 Then [Version] End) [2nd Max]

From

(

Select

[State]

,[Version]

,(Select Count(*) From #data Sub Where Sub.[Version] >= Main.[Version]

And Sub.[State] = Main.[State]) RowId

From

#data Main

) as data

Group By

[State]

|||

wow!!!!!!!

Thanks a lot, Sekar

I got my answer from your nice queries.

Once again Thanks

With Regards

VJ

|||

Try:

Code Snippet

createtable dbo.t1 (

[State] char(2)notnull,

Version intnotnull

)

go

insertinto dbo.t1 values('CA', 19)

insertinto dbo.t1 values('CA', 8)

insertinto dbo.t1 values('CA', 15)

insertinto dbo.t1 values('CA', 6)

insertinto dbo.t1 values('MA', 12)

insertinto dbo.t1 values('MA', 7)

insertinto dbo.t1 values('MA', 10)

insertinto dbo.t1 values('MA', 5)

go

select

[State],

max(version)as [1st Maximum],

min(version)as [2nd Maximum]

from

(

select

[state], version

from

dbo.t1 as a

where

version in(

selecttop 2 b.version

from dbo.t1 as b

where b.[state] = a.[state]

orderby b.version desc

)

)as t

groupby

[state]

orderby

[state]

go

-- 2005 - 1

;with cte

as

(

select

[state], version,

row_number()over(partitionby [state] orderby version desc)as rn

from

dbo.t1

)

select

[state],

max(version)as [1st Maximum],

min(version)as [2nd Maximum]

from

cte

where

rn <= 2

groupby

[state]

orderby

[state]

go

-- 2005 - 2

select

a.[state],

max(c.version)as [1st Maximum],

min(c.version)as [2nd Maximum]

from

(

selectdistinct

[state]

from

dbo.t1

)as a

crossapply

(

selecttop 2

b.version

from

dbo.t1 as b

where

b.[state] = a.[state]

orderby

b.version desc

)as c

groupby

a.[state]

orderby

a.[state]

go

droptable dbo.t1

go

AMB