Trying to get a single row in a query?

Question

I am trying to get a single record in the case statement, where am i suppose to put the seelect statement top(1)

 CASE WHEN CAST(VehicleCC AS MONEY) BETWEEN -1 AND 50 THEN '33.94'
                          WHEN CAST(VehicleCC AS MONEY) BETWEEN 51 AND 125 THEN '39.65' 
                          WHEN CAST(VehicleCC AS  MONEY) BETWEEN 126 AND 500 THEN '55.90' 
                          WHEN CAST(VehicleCC AS  MONEY) BETWEEN 501 AND 650 THEN '67.31'
                          WHEN CAST(VehicleCC AS MONEY) BETWEEN 651 AND 1000 THEN '78.65' 
                          WHEN CAST(VehicleCC AS MONEY ) > 1001 THEN '79.85' 
                          else 
                           (SELECT TOP(1) cast (VehicleCC as varchar (50)) FROM  HIRE_INSTRUCTION HIRP 
                              INNER JOIN VEHICLE VEH ON HIRP.FKFleetVehicleID=VEH.vehicleid
                              WHERE HIRP.FKBLDRefID=cl.FKBLDRefID AND HIRP.Deleted=0 AND VEH.Deleted=0
                                ORDER BY veh.VehicleCC DESC) END AS [Hire Rate Charged Per Day],

Use LIMIT 1 see here http://stackoverflow.com/questions/4708708/mysql-order-by-limit — Bhavik Shah, Oct 25 '13 at 15:32
OP said tsql which implies MS SQL Server. I don't believe `LIMIT` exists in SQL Server. — Sir Crispalot, Oct 25 '13 at 15:36
I have one vehicle table that house both customer, thirdparty, hire vehcles, when a vehicle is hired, sometimes they might swapped for another so i am grading vehicleCC BETWEEN those values though the data type is int but am returning just money base on the query — Alora101, Oct 25 '13 at 16:23

score 0 · Accepted Answer · answered Oct 25 '13 at 23:37

If TOP(1) isn't working, try RANK():

SELECT VehicleCC
FROM (
    SELECT CAST(VEH.VehicleCC as varchar (50)),
        RANK() OVER(ORDER BY CAST(VEH.VehicleCC as varchar (50))) AS CCRank
    FROM  HIRE_INSTRUCTION HIRP 
        INNER JOIN VEHICLE VEH 
            ON HIRP.FKFleetVehicleID=VEH.vehicleid
    WHERE HIRP.FKBLDRefID=cl.FKBLDRefID 
        AND HIRP.Deleted=0 
        AND VEH.Deleted=0
    ORDER BY veh.VehicleCC DESC
    ) CC
WHERE CCRank = 1

score 0 · Answer 2 · edited Oct 26 '13 at 04:09

0

You can use set rowcount to limit the number of rows returned by a query.

set rowcount 1 -- the query will return one record

select * from table 

set rowcount 0 --back to normal

edited Oct 26 '13 at 04:09

Edper

9,144
1
27
46

answered Oct 26 '13 at 03:42

greyalien007

510
4
13

Trying to get a single row in a query?

2 Answers2