Raul Gonzales Raul Gonzales - 2 months ago 6
SQL Question

How to create a column with CAST data and get the top 5 results?

I have a column that has alphanumerical data ([Usage_in_GB]) and when i want to sort it descending is not doing it. i have found on SO answers to this question but none of them suit my needs so what i did is convert the alphanumeric column ([Usage]) to a float but now i cant get the top 5 results.

here is my query:

SELECT
[Name],
[Model],
[Usage_in_GB],
[Number_of_Clients],
[Date],
CAST (REPLACE([Usage_in_GB],'GB','') AS FLOAT) AS [Usage]
FROM[dbo].[Devices]
WHERE([Usage] IN (SELECT TOP 5 [Usage]
FROM [dbo].[Devices]
GROUP BY [Usage]
ORDER BY [Usage] DESC)
)

Answer

You are in the good way just try in this way:

SELECT TOP 5
    [Name],
    [Model],
    [Usage_in_GB],
    [Number_of_Clients],
    [Date],
    CAST(REPLACE([Usage_in_GB],'GB','') AS FLOAT) AS [Usage]
FROM[dbo].[Devices]
ORDER BY [Usage] DESC
Comments