Confusing with Top 1 function

Question

Confusing with Top 1 function

WhiteLotus

SSChampion

Points: 12120
More actions
August 28, 2014 at 5:33 pm

#310500

I have a table as below
A B
1Score= 90
2Score = 70
I would like to get the first record , especially the value of column B which is 90
I did a query as below :
Select top 1 (B) from Table
The result is Score=90 .
But what I want is only 90
How is the script ? if I use the right function , how the script look like ?
Please kindly response
Thanksss

Viewing 3 posts - 1 through 2 (of 2 total)

You must be logged in to reply to this topic. Login to reply

Luis Cazares SSC Guru Points: 183698 More actions · Answer 1

The RIGHT function is one option, you could also use STUFF function. Additionally, you could cast your result as a numeric data type.

However, the most important thing to remember is that you shouldn't use TOP without ORDER BY unless that you want unexpected results.

Here are some ideas of what you could use.

CREATE TABLE SampleData(

A int,

B varchar(50)

)

INSERT INTO SampleData

VALUES

(1,'Score= 90'),

(2,'Score = 70')

SELECT *,

STUFF(B, 1, CHARINDEX('=', B), ''), -- As string using STUFF

RIGHT(B, LEN(B) - CHARINDEX('=', B)), --As string using RIGHT

CAST( STUFF(B, 1, CHARINDEX('=', B), '') AS int) --As int

FROM SampleData

ORDER BY A

GO

DROP TABLE SampleData

Have you seen that I included consumable sample data that includes CREATE TABLE and INSERT statements? You're supposed to do this to prevent us from spending time creating the scenario.

Luis C.
General Disclaimer:
Are you seriously taking the advice and code from someone from the internet without testing it? Do you at least understand it? Or can it easily kill your server?

How to post data/code on a forum to get the best help: Option 1 / Option 2

WhiteLotus SSChampion Points: 12120 More actions · Answer 2

Luis Cazares (8/28/2014)
The RIGHT function is one option, you could also use STUFF function. Additionally, you could cast your result as a numeric data type.
However, the most important thing to remember is that you shouldn't use TOP without ORDER BY unless that you want unexpected results.
Here are some ideas of what you could use.
CREATE TABLE SampleData(
A int,
B varchar(50)
)
INSERT INTO SampleData
VALUES
(1,'Score= 90'),
(2,'Score = 70')
SELECT *,
STUFF(B, 1, CHARINDEX('=', B), ''), -- As string using STUFF
RIGHT(B, LEN(B) - CHARINDEX('=', B)), --As string using RIGHT
CAST( STUFF(B, 1, CHARINDEX('=', B), '') AS int) --As int
FROM SampleData
ORDER BY A
GO
DROP TABLE SampleData
Have you seen that I included consumable sample data that includes CREATE TABLE and INSERT statements? You're supposed to do this to prevent us from spending time creating the scenario.

Thanks for your quick response . Appreciate it !

OK noted from your suggestion .

my script run successfully 🙂