Ordinal Question - 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

Ordinal Question

Author

Topic

rpc86
Posting Yak Master

200 Posts

Posted - 2006-08-29 : 04:15:16

Hi guys,

How to produce ordinal numbers in sql query?

Say I have a table for a Building. My floor numbers are intergers, 1,2,3,4 and so on. When I run the query, the result I need is like this:

1st
2nd
3rd
4th
5th
...
23rd
....

How will I do this?

Thank you.

Lumbago
Norsk Yak Master

3271 Posts

Posted - 2006-08-29 : 05:05:30

How is this:

DECLARE @table table (FloorNumber int)
DECLARE @counter int

SET @Counter = 1

WHILE @Counter < 35
  BEGIN
    INSERT INTO @Table SELECT @Counter
    SET @Counter = @Counter + 1
  END

SELECT FloorNumber, Ordinal = CASE
  WHEN FloorNumber BETWEEN 11 AND 20 THEN 'th'
  WHEN RIGHT(FloorNumber, 1) = 0 THEN 'th'
  WHEN RIGHT(FloorNumber, 1) = 1 THEN 'st'
  WHEN RIGHT(FloorNumber, 1) = 2 THEN 'nd'
  WHEN RIGHT(FloorNumber, 1) = 3 THEN 'rd'
  WHEN RIGHT(FloorNumber, 1) > 3 THEN 'th'
  END
FROM @table

--
Lumbago
"Real programmers don't document, if it was hard to write it should be hard to understand"

Lumbago
Norsk Yak Master

3271 Posts

Posted - 2006-08-29 : 05:07:58

How many floors do you have? Seems like it's working up until 110th floor..gets messed up on the 111th.

--
Lumbago
"Real programmers don't document, if it was hard to write it should be hard to understand"

Lumbago
Norsk Yak Master

3271 Posts

Posted - 2006-08-29 : 05:10:27

Should work now for unlimited floors I belive...

DECLARE @table table (FloorNumber int)
DECLARE @counter int

SET @Counter = 1

WHILE @Counter < 200
  BEGIN
    INSERT INTO @Table SELECT @Counter
    SET @Counter = @Counter + 1
  END

SELECT FloorNumber, Ordinal = CASE
  WHEN RIGHT(FloorNumber, 2) BETWEEN 11 AND 20 THEN 'th'
  WHEN RIGHT(FloorNumber, 1) = 0 THEN 'th'
  WHEN RIGHT(FloorNumber, 1) = 1 THEN 'st'
  WHEN RIGHT(FloorNumber, 1) = 2 THEN 'nd'
  WHEN RIGHT(FloorNumber, 1) = 3 THEN 'rd'
  WHEN RIGHT(FloorNumber, 1) > 3 THEN 'th'
  END
FROM @table

--
Lumbago
"Real programmers don't document, if it was hard to write it should be hard to understand"

rpc86
Posting Yak Master

200 Posts

Posted - 2006-08-29 : 05:14:48

Thank you for your help. It seems that the last advice is much good.

Thank you and more power.

SwePeso
Patron Saint of Lost Yaks

30421 Posts

Posted - 2006-08-29 : 05:26:11

How about this?

SELECT DISTINCT	Number,
		CASE
			WHEN Number % 100 IN (11, 12, 13) THEN 'th'
			WHEN Number % 10 = 1 THEN 'st'
			WHEN Number % 10 = 2 THEN 'nd'
			WHEN Number % 10 = 3 THEN 'rd'
			ELSE 'th'
		END Ordinal
FROM		master..spt_values
WHERE		Number BETWEEN 1 AND 255
ORDER BY	Number

Peter Larsson
Helsingborg, Sweden

Lumbago
Norsk Yak Master

3271 Posts

Posted - 2006-08-29 : 05:37:18

You have the same problem as I initially did...check out floor # 111. It says st when it should be th

--
Lumbago
"Real programmers don't document, if it was hard to write it should be hard to understand"

khtan
In (Som, Ni, Yak)

17689 Posts

Posted - 2006-08-29 : 05:47:26

The world tallest building has 101 floors and Sears Tower rank no 4 with 110 stories. Your query will still hold

SwePeso
Patron Saint of Lost Yaks

30421 Posts

Posted - 2006-08-29 : 05:49:19

What about the one building in Shang Hai?
600 floors?

Peter Larsson
Helsingborg, Sweden

SwePeso
Patron Saint of Lost Yaks

30421 Posts

Posted - 2006-08-29 : 05:50:34

quote:
Originally posted by Lumbago

You have the same problem as I initially did...check out floor # 111. It says st when it should be th

Not anymore, check out my revised query...

Peter Larsson
Helsingborg, Sweden

khtan
In (Som, Ni, Yak)

17689 Posts

Posted - 2006-08-29 : 05:51:10

Jin Mao Building, Shanghai 1999 88 stories 421 m 1,381 ft

http://www.infoplease.com/ipa/A0001338.html

SwePeso
Patron Saint of Lost Yaks

30421 Posts

Posted - 2006-08-29 : 05:58:19

What about Burj Dubai? 162 floors.
Taipei 101? 101 floors.

Peter Larsson
Helsingborg, Sweden

khtan
In (Som, Ni, Yak)

17689 Posts

Posted - 2006-08-29 : 06:17:04

quote:
Originally posted by Peso

What about Burj Dubai? 162 floors.
Taipei 101? 101 floors.

Peter Larsson
Helsingborg, Sweden

Wow ! Burj Dubai is at least 700 m. http://en.wikipedia.org/wiki/Al_Burj

Yes Taipei 101 is 101 floors

The highest i ever been up to is Menara Telekom Headquarters, Kuala Lumpur, Malaysia. 310 m.

Lumbago
Norsk Yak Master

3271 Posts

Posted - 2006-08-29 : 06:21:14

Better to be safe than sorry...that's why I always use bigint for my identity-columns (KIDDING!

)

--
Lumbago
"Real programmers don't document, if it was hard to write it should be hard to understand"

SwePeso
Patron Saint of Lost Yaks

30421 Posts

Posted - 2006-08-29 : 06:25:23

The highest building I've been to is Las Vegas Stratosphere, 1149 ft (350 m).
And I also did the roller coaster on top and the Space Shot. Talk about "I can see my house from heeeeeeere..."

Peter Larsson
Helsingborg, Sweden

khtan
In (Som, Ni, Yak)

17689 Posts

Posted - 2006-08-29 : 08:17:43

quote:
Originally posted by Peso

The highest building I've been to is Las Vegas Stratosphere, 1149 ft (350 m).
And I also did the roller coaster on top and the Space Shot. Talk about "I can see my house from heeeeeeere..."

Peter Larsson
Helsingborg, Sweden

Argh ! you beat me to it. I will be in Kuala Lumpur next week. Will try to go to the Petronas Twin Tower.

Arnold Fribble
Yak-finder General

1961 Posts

Posted - 2006-08-29 : 08:43:04

Aren't Al Burj and Burj Dubai two different projects?

Ob height: 447m (CN Tower SkyPod).

Lumbago
Norsk Yak Master

3271 Posts

Posted - 2006-08-29 : 08:52:59

Hm...does Oslo Plaza count?? 34 stories and 117 meters

Well, I have been in the empire state building and the tallest building in bankok...no where near any of your suggestions I'm afraid...

--
Lumbago
"Real programmers don't document, if it was hard to write it should be hard to understand"

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources