Hi All,
I have alphanumeric data in a Table Assets. The column name is Milepost.

Column Values are like below

MilePost

1.24
1.61
4.56
4.78
5.45
6.91
7.19
9.7
11.31
12.96
13.49
13.7
15.4
15.83
18.5
27.11
27.31
30
33.1
34.7
94.9
98
99.55
104.08
104.42
104.55
105.1
209.1
NULL
343.1
LR 1.36
LR 1.90
364.91
364.9
YARD
LR 12.80
LR 1.0
LR 4.41
368.6
342.4
342.57

Now I want to select records between 1 to LR 4.41 which should return all records between 1 to LR 4.41

My below query is not returning proper values. So i need a correct sql query.

Select Milepost from Assets where Milepost between '1' and 'LR 4.41'


Thanks

What do you mean by "not returning proper values?"

Show us what it is returning, and show us what you expect it to return, and maybe we can come up with a solution for you.

Right now we have no idea what you expect to see and how that differs from what you are seeing (although I certainly have a hunch as to what the issue is).

i want values to be returned in sorted order as (say for example) and values in between 1 to LR 4.41
1
1.2
1.4
1.6
2
2.1
K1.2
K1.4
LR 1.3
LR 1.41
LR 4.41

But it is returning values which are not in between 1 to LR 4.41

Say for example

1
1.2
MP 1.34
KY 1.34
2.5

I dont want values MP 1.34 and KY 1.34 which comes after LR 4.41 to be returned.

So my query is : Select Milepost from Assets where Milepost between '1' and 'LR 4.41

I don't think it is doing what you say it is doing.

For instance, in your list of "improper" values in your last post, you say it is returning MP 1.34.

I don't think it is. I think you just wrote that value in an attempt to make a point, but I don't think that value actually resulted from your query.

I just created a temporary table, inserted your full list of values, and ran the query you gave, and MP 1.34 was not one of the values returned.

So, again, show us the list of values that you expect to see, and the list of values that you are seeing, and we can give you an answer.

Ken,
Thank u for all ur reply. See today is sunday and i am not office so i have given some guessed values which came in my mind. I will provide u correct data on monday.

Now can you please let me know query which returns values between mile post
number(say 1) and alphabet.(say MP 1.34)
See the milepost list is not sorted order in database.

tHANKS