CODEWITHSUNDEEP
sql-server
Keshin
Keshin

Reputation: 29

Select Top 1 In SQL Server

Please help me in select top 1

The data like this

Code    Amp      Price
-----------------------
00001   10       1000
00002   75-100   1500
00003   50-60    1200
00004   15       1100

Note : datatype for column Amp is VarChar

I want to select with Amp 75 and I want get the price is 1500

So I use this statement:

SELECT TOP 1 * 
FROM Cable 
WHERE (Amp <= '75') 
ORDER BY Amp DESC

but the result price I get is 1200 is record with code : 00003 (wrong), actually I want the result is code : 00002 and the price is 1500

But if I want to select with Amp 76 the result is true with the syntax :

SELECT TOP 1 * 
FROM Cable 
WHERE (Amp <= '75') 
ORDER BY Amp DESC

What is the true select for my case? Please help me

Upvotes: 2

Views: 6842

Answers (5)

FLICKER
FLICKER

Reputation: 6683

If you are using SQL Server 2008 and later, try something like this:

SELECT TOP 1 * 
FROM Cable 
WHERE isnumeric(left(Amp, 2)) = 1 and cast(left(Amp, 2) as int) <= 75
and Price = 1500 
ORDER BY Amp DESC

Note: This will work only if you have no records with Amp less than 10.

Upvotes: 0

Keshin
Keshin

Reputation: 29

Thank's for all answers. I decides to type all data and change it with two field.

Code Amp1 Amp2 Price

00001 10 10 1000 00002 75 100 1500 00003 50 60 1200 00004 15 15 1100

The single value i type same in field Amp2 and then i use the syntax : SELECT * FROM Cable WHERE (65 BETWEEN Amp1 AND Amp2)

Upvotes: 0

tarheel
tarheel

Reputation: 4797

The problem is that SQL Server is not going to sort a varchar column like an int.

Sorting issue example:

select *
from (
    select '125' as nbr
    union all
    select '24' as nbr
    ) as a
order by a.nbr asc

1 is less than 2 (the first character in each nbr), so it will sort thinking that 125 < 24 (not true), even though it looks pretty simple to anyone that 24 should show up first, which is how it would be sorted if the datatype of the column were an int.

What needs to happen is to split the amp column into ranges, or max and min. Using the - as the delimeter, you can use charindex to split the numbers up and cast them as ints instead.

Sample Data Setup:

declare @cable table
    (
        code char(5) not null
        , amp varchar(10) not null
        , price int not null
    )

insert into @cable
values 
        ('00001','10'       ,10000),
        ('00002','75-100'   ,15000),
        ('00003','50-60'    ,12000),
        ('00004','15'       ,11000)

Answer:

declare @amp_nbr int = 75

select top 1 *
from (  
    select c.code
    , cast(iif(charindex('-', c.amp, 0) > 0, left(c.amp, charindex('-', c.amp, 0) - 1), c.amp) as int) as amp_min
    , cast(iif(charindex('-', c.amp, 0) > 0, right(c.amp, len(c.amp) - charindex('-', c.amp, 0)), c.amp) as int) as amp_max
    , c.price
    from @cable as c
    ) as a
where 1=1
and @amp_nbr between a.amp_min and a.amp_max
order by a.amp_min desc

After that, a simple between constraint in the where clause will do the trick.

Upvotes: 0

John Cappelletti
John Cappelletti

Reputation: 81930

Just about any parse/split function will do, and combined with a Cross Apply, it becomes a small matter

-- Easy to do without a parse function

Declare @Cable table (Code varchar(25),Amp varchar(50),Price int)
Insert Into @Cable values
('00001','10',    1000),
('00002','75-100',1500),
('00003','50-60', 1200),
('00004','15',    1100)

Select Top 1 A.*
 From  @Cable A
 Cross Apply [dbo].[udf-Str-Parse](A.Amp,'-') B
 Where RetVal<=76              --<< Notice we are testing for 76
 Order By Price Desc

Returns

Code    Amp     Price
00002   75-100  1500

The UDF if interested

CREATE FUNCTION [dbo].[udf-Str-Parse] (@String varchar(max),@Delimiter varchar(25))
Returns Table 
As
Return (  
    with   cte1(N)   As (Select 1 From (Values(1),(1),(1),(1),(1),(1),(1),(1),(1),(1)) N(N)),
           cte2(N)   As (Select Top (IsNull(DataLength(@String),0)) Row_Number() over (Order By (Select NULL)) From (Select N=1 From cte1 a,cte1 b,cte1 c,cte1 d) A ),
           cte3(N)   As (Select 1 Union All Select t.N+DataLength(@Delimiter) From cte2 t Where Substring(@String,t.N,DataLength(@Delimiter)) = @Delimiter),
           cte4(N,L) As (Select S.N,IsNull(NullIf(CharIndex(@Delimiter,@String,s.N),0)-S.N,8000) From cte3 S)

    Select RetSeq = Row_Number() over (Order By A.N)
          ,RetVal = LTrim(RTrim(Substring(@String, A.N, A.L)))
    From   cte4 A
);
--Orginal Source http://www.sqlservercentral.com/articles/Tally+Table/72993/
--Much faster than str-Parse, but limited to 8K
--Select * from [dbo].[udf-Str-Parse-8K]('Dog,Cat,House,Car',',')
--Select * from [dbo].[udf-Str-Parse-8K]('John||Cappelletti||was||here','||')

Upvotes: 2

Rajesh Bhat
Rajesh Bhat

Reputation: 811

If you have to work with this existing datatype and table structure then below query may work for you.

SELECT TOP 1 *
FROM Cable
WHERE (SUBSTRING(Amp,1,IIF((CHARINDEX('-',Amp)-1)>0,(CHARINDEX('-',Amp)-1),0 ) ) <=75)
ORDER BY Amp DESC

Upvotes: 1

Related Questions