Query does not sort records by Asc/Desc

Asked

Viewed 676 times

2

I have a table called cadcha (registration of call) in the database where all calls made by employees of a particular company are stored.

cadcha
--------------------------------------------------
nreg    |telefone      |telpretot    |ramaldestino
1110     35420000       0,79          5065

telpretot = link value

I performed a query that brings the statistic fashion by the value of the links, and needed it to be ordered by the decreasing quantity, ie the record with the largest quantity, comes first.

SELECT COUNT(telpretot) AS qtde, 
(CAST(telpretot AS DECIMAL(18,0))) as       preco
FROM cadcha
WHERE teldata = '08/03/2015'
GROUP BY (CAST(telpretot AS DECIMAL(18,0)))
ORDER BY qtde DESC;

It is working ok, but the order record is returned from minor to major. I have tried to change the DESC to ASC but the displayed result is the same.

inserir a descrição da imagem aqui

How can I fix the script so that the records are ordered from the highest to the lowest?

sql firebird

  • takes group by and tries again.

  • what kind of field telpretot ?

2 answers

1

This happens because the column Qtde is being filled in at runtime, because you are using COUNT(telpretot), try that way.

declare @cadcha table
(
  nreg int,
  telefone int,
  telpretot numeric(18,2),
  ramaldestino int,
  teldata date

)

insert into @cadcha values
(1110,     35420000 ,      0.79   ,       5065, '08/03/2015'),
(1110,     35420000 ,      0.3   ,       5065, '08/03/2015'),
(1110,     35420000 ,      44.79   ,       5065, '08/03/2015'),
(1110,     35420000 ,      44.79   ,       5065, '08/03/2015'),
(1110,     35420000 ,      0.5   ,       5065, '08/03/2015'),
(1110,     35420000 ,      44.79   ,       5065, '08/03/2015'),
(1110,     35420000 ,      44.79   ,       5065, '08/03/2015'),
(1110,     35420000 ,      44.79   ,       5065, '08/03/2015'),
(1110,     35420000 ,      5.79   ,       5065, '08/03/2015'),
(1110,     35420000 ,      6.79   ,       5065, '08/03/2015'),
(1110,     35420000 ,      44.79   ,       5065, '08/03/2015'),
(1110,     35420000 ,      44.79   ,       5065, '08/03/2015'),
(1110,     35420000 ,      44.79   ,       5065, '08/03/2015'),
(1110,     35420000 ,      10.79   ,       5065, '08/03/2015'),
(1110,     35420000 ,      44.79   ,       5065, '08/03/2015'),
(1110,     35420000 ,      44.79   ,       5065, '08/03/2015'),
(1110,     35420000 ,      110.79   ,       5065, '08/03/2015'),
(1110,     35420000 ,      32.79   ,       5065, '08/03/2015'),
(1110,     35420000 ,      44.79   ,       5065, '08/03/2015'),
(1110,     35420000 ,      44.79   ,       5065, '08/03/2015'),
(1110,     35420000 ,      32.79   ,       5065, '08/03/2015'),
(1110,     35420000 ,      44.79   ,       5065, '08/03/2015'),
(1110,     35420000 ,      44.79   ,       5065, '08/03/2015'),
(1110,     35420000 ,      34.79   ,       5065, '08/03/2015'),
(1110,     35420000 ,      44.79   ,       5065, '08/03/2015'),
(1110,     35420000 ,      44.79   ,       5065, '08/03/2015'),
(1110,     35420000 ,      44.79   ,       5065, '08/03/2015'),
(1110,     35420000 ,      44.79   ,       5065, '08/03/2015')

select * from 
(
    SELECT COUNT(telpretot) AS qtde, 
    (CAST(telpretot AS DECIMAL(18,0))) as       preco
    FROM @cadcha
    WHERE teldata = '08/03/2015'
    GROUP BY (CAST(telpretot AS DECIMAL(18,0)))
)D
ORDER BY qtde DESC;

inserir a descrição da imagem aqui

  • He rode well, but he continues ordering only from the smallest to the largest.

  • you can post your select data without group by only the data from the SELECT telpretot AS qtde, 
(CAST(telpretot AS DECIMAL(18,0))) as preco
FROM cadcha
WHERE teldata = '08/03/2015

  • 1

    @antunesleo_ I made a test using the sql server and I didn’t have that problem, maybe the database you’re using might have some implication for not being ordered the way you want it to be.

  • I also suspect that it is some problem with the SBDB (Firebird) or some limitation of the same, because when I order the query by field preco both ASC/DESC function, but by field qtde It always comes back ordered from the smallest to the greatest. Anyway, your help has opened my head to another logic that worked here, thanks for the help!

  • @antunesleo_ the telpretot field is of what type?:

  • guy DOUBLE PRECISION

  • you already tried to withdraw the CAST country telpretot to see how he behaves?

  • 1

    I found out where the problem was. In the system when you are logged in as ADM, there is a screen where we can run SQL scripts and were performing all the tests there. I did a test using Ibexpert and the result came ordered from the highest to the lowest, as I needed!

Show 3 more comments

0

And this group by ai, remove it

SELECT COUNT(telpretot) AS qtde, 
(CAST(telpretot AS DECIMAL(18,0))) as preco
FROM cadcha
WHERE teldata = '08/03/2015'
ORDER BY qtde DESC;

  • I need group by na query to group by value, for example, I group all 1 real links and how many links. Group 2 real links and count how many links. Would you sort this with the group by?

Browser other questions tagged sql firebird

You are not signed in. Login or sign up in order to post.