Why when I execute the query below is the output order unusual? I know it's supposed to have an 'order by' if I really wanted it in order, but I'm curious.
tsql "select avg(Cnt),Ldate from sitelog where Service='' and Site='www' group by month(Ldate)"
As you mention the ordering is undefined unless you specify an order by. In the case of the group by the results are often ordered by what you are grouping by, in this case month(Ldate), so it is not particularly unusual.
Hi,
I am getting erroneous results when I do a group by on a varchar field.
My query is,
select keywords, count(*) from query_log
group by keywords
order by 2 desc
some of the results show,
keywords count(*)
------------+------------+
felix the cat 422
production server 347
deburring 283
corporate store 244
..........
but when i do a count(*) on the above keywords, I get only 1. why am I getting such huge values?
ok, my query was,
select count(*) from query_log where keywords='production server'
AND
the result was,
count(*)
------------+
1
The "group by" showed it as 347.
i ran it on the version,
Texis Web Script (Vortex) Copyright (c) 1996-2000 Thunderstone - EPI, Inc.
Commercial Version 3.01.975518872 of Nov 29, 2000 (sparc-sun-solaris2.6)
but I do have a latest version,
Texis Web Script (Vortex) Copyright (c) 1996-2001 Thunderstone - EPI, Inc.
Commercial Version 3.01.987613528 of Apr 18, 2001 (sparc-sun-solaris2.6)
We can't replicate the problem with either version. What indexes, if any, are there on query_log, in particular on keywords? If you try the query without the "order by " what are the results?
Basically, I am logging all the keywords entered for search, by users, in a log file. I want to find out the queries which are searched most in a particular month. What I did was imported all the data from the log file into the table query_log (id counter, keywords varchar(200)) using the timport functionality.
I imported the data after removing the leading and trailing spaces and coverting all of them to lower case.
There are no indexes in the table. Then i wanted to find out the number of times each distinct query occured in the table using the sql query with group by clause. That is when I got improper results.
I am also doing a group by on an integer field in one of my programs and there is no problem in that.
I dont know why, but when I added a where clause, it seemed to work ok. This was the query which did ok,
select count(*), keywords from query_log
where keywords matches 'a%' or keywords matches 'b%'
.......
group by keywords
order by 1 desc
