Help with a SELECT statment - SQL Server Forums

Please start any new threads on our new site at https://forums.sqlteam.com. We've got lots of great SQL Server experts to answer whatever question you can come up with.

All Forums

General SQL Server Forums

New to SQL Server Programming

Help with a SELECT statment

Author

Topic

diabolic
Starting Member

4 Posts

Posted - 2007-07-17 : 11:15:48

Hi all,
i'm new to SQL but i've been asked to write an SQL statement to select the latest numeric version value(in this case version 3) from this table, any help?

Name Version Episode

John 4c 60

John 4b 50

John 4a 40

John 3 30

John 2 20

John 1 10

Regards

khtan
In (Som, Ni, Yak)

17689 Posts

Posted - 2007-07-17 : 11:17:18

[code]SELECT MAX(Version)
FROM this_table
WHERE isnumeric(Version) = 1[/code]

KH
[spoiler]Time is always against us[/spoiler]

diabolic
Starting Member

4 Posts

Posted - 2007-07-17 : 11:22:14

Thanks Khtan for your quick reply,
is the where isnumeric (version)=1 switch to check if the value is numeric and take the max, and is it set to one which means ON?
so if the table name is Johndocs the statement will be
SELECT MAX Version from Johndocs WHERE isnumeric Version=1
is that right?

Thanks

SwePeso
Patron Saint of Lost Yaks

30421 Posts

Posted - 2007-07-17 : 11:40:26

SELECT MAX(Version)
FROM this_table
WHERE Version NOT LIKE '%[^0-9]%'

Just in case there is a version 4e

Peter Larsson
Helsingborg, Sweden

diabolic
Starting Member

4 Posts

Posted - 2007-07-17 : 11:44:16

Thanks Peter,
i want to select only numeric value, so only 3 i don't want to select 4a or 4b but only versions with only numbers. Is that what u meant?

Regards

SwePeso
Patron Saint of Lost Yaks

30421 Posts

Posted - 2007-07-17 : 12:22:20

No. 4e can be treated as a numeric value with the ISNUMERIC function.

Peter Larsson
Helsingborg, Sweden

madhivanan
Premature Yak Congratulator

22864 Posts

Posted - 2007-07-18 : 03:38:39

Isnumeric is not reliable

Select data from
(
select ' 943 ' as data
union all
select '345'
union all
select '$345,456'
union all
select '12d2'
) T
where isnumeric(data)=1

Madhivanan

Failing to plan is Planning to fail

diabolic
Starting Member

4 Posts

Posted - 2007-07-18 : 05:00:16

Thanks Mad but can i understand how is your statement working?

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources