Results 1 to 3 of 3
  1. #1
    Join Date
    Mar 2012
    Posts
    12

    Unanswered: select with group by, ignore where clause

    I have a strange behavior for my Sybase 15.7 databases.

    When I execture the following :
    Code:
    select * from master..sysusages where segmap != 4
    It works correctly, result ignore every line with segmap = 4.

    ie (output from dbid 4 only)
    dbid,segmap,lstart,size,vstart,location,unreserved pgs,crdate,vdevno
    4,3,0,5120,0,0,565,2013-04-22 16:05:36.346,4
    ...
    4,3,5120,2560,5733376,0,2048,2013-04-22 16:05:36.346,4

    The minute I had a group by dbid, I get this, only for dbid 4 :

    Code:
    select * from master..sysusages where segmap != 4 group by dbid
    dbid,segmap,lstart,size,vstart,location,unreserved pgs,crdate,vdevno
    4,3,0,5120,0,0,565,2013-04-22 16:05:36.346,4
    4,3,5120,2560,5733376,0,2048,2013-04-22 16:05:36.346,4
    4,4,7680,2560,0,0,2550,2013-04-22 16:05:36.346,5
    4,4,10240,2560,713728,0,2550,2013-04-22 16:05:36.346,5

    As you can see in the result above, the rows where segmap != 4 are now listed in the select even though I explicitely ask for not listing them.

    Anyone have any idea why I'm getting this behavior?

  2. #2
    Join Date
    Feb 2015
    Posts
    24
    It is not a problem of ASE 15.7..It is a problem with the command..

    The reason behind this is because of using group by condition with respect to dbid ..due to this the where condition is being over-written with the group by clause..
    to avoid this try..
    select * from sysusages where segmap!=4 group by segmap order by dbid

    A small doubt though..
    what is it that you want to achieve with that select statement ?
    for what purpose are you using it?

    PS: use group by only with aggregate functions viz., sum(),count(),min(),max()..
    Last edited by radek854; 02-20-15 at 00:17.

  3. #3
    Join Date
    Mar 2012
    Posts
    12
    Thanks you radek, you got it right!

    The fact is, SQL is not something I have been handling for a long time and I am still learning.

    The long term goal of this is to create a script that will be launched at space threshold to warn me of space issues.

    I was having issues with the math so to verify myself I wanted to do the math manually to validate my script so far and this is where I ended up with this behavior with the group by that I did not understand. In fact, I was trying to do with the "group by" what you perfectly did with "order by".

    Thanks again for your help, lesson learned!

    Cheers!

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •