Another SQL Question

[zlander79]

With the database I am working with, there are two objects with a one to many relationship (each gene can have a bunch of binding sites). I want to make a query that will only return the highest score (a column in binding site) binding site for each gene. I'm not sure how to go about this though. Any Ideas?

[Vertigo Boost]

Shot in the dark, but try returning with MAX?

http://www.techonthenet.com/sql/max.php

[OS-Wiz]

MAX might work, but that's based on the complexity of the query. Can you show us the two table schemas please? And then state, in words, exactly what you need. What version of SQL (Oracle, MySQL, SQL Server, whatever). More than likely you'll first have to get the MAX highest score, then join that result with the main query.

Something like (Oracle example):

WITH DATA AS MAX_SCORE
SELECT GENE, MAX(SCORE)
FROM BINDING
SELECT GENE.A, SCORE.B
FROM GENE A, MAX_SCORE B
WHERE GENE.A = GENE.B

[zlander79]

select `g`.`id`          AS `ID`,  
       `g`.`Name`        AS `NAME`, 
       `c`.`cog`         AS `COG`, 
       `o`.`ortho`       AS `ORTHO`, 
       `os`.`site`       AS `OPSITEID`, 
       `s`.`Sequence`    AS `SITE`, 
       max(`s`.`Score`)  AS `SCORE`, 
       `op`.`id`         AS `OPID`, 
       `op`.`prime_gene` AS `OPPRMGN`, 
       `go`.`ortho`      AS `ORTHO` 
 
from(((((((`METAGENO`.`GENE` `g`) 
      join `METAGENO`.`ORTHO` `o`) 
      join `METAGENO`.`GENE_COG` `c`) 
      join `METAGENO`.`OPERON_SITE` `os`) 
      join `METAGENO`.`OPERON` `op`) 
      join `METAGENO`.`GENE_ORTHO` `go`) 
      join `METAGENO`.`SITE` `s`) 
      where( 
                (`op`.`id`     = `g`.`Operon`) 
            and (`os`.`operon` = `g`.`Operon`) 
            and (`go`.`gene`   = `g`.`id`) 
            and (`c`.`gene`    = `g`.`id`) 
            and (`s`.`id`      = `os`.`site`)) GROUP BY `op`.`id`

This is what I was trying last night but I wasn't having any success. When I leave out the "GROUP BY `op`.`id` I just get a single entry for the highest score. I need the highest one per op.id.

[OS-Wiz]

Your select is retrieving only those that match the where clause AND have the single highest score. In other words, if there where five entries for a match to your where clause, the select max(`s`.`Score`) is grabbing just the one of the five matched with the highest score. Can you use GROUP BY `op`.`id` HAVING MAX (`s`.`Score`) in your version of SQL syntax and leave out the select max(`s`.`Score`), just use `s`.`Score`?

[zlander79]

Here is what I'm doing now, I will also run your suggestion and see what comes up.

select `g`.`id`       AS `ID`,  
       `g`.`Name`     AS `GENE`, 
       `o`.`id`       AS `OPID`, 
       `os`.`site`    AS `BSID`, 
       `s`.`Sequence` AS `SITE`, 
       `s`.`Score`    AS `SCORE` 
 
    from((((  
              `METAGENO`.`GENE`        `g` ) 
         join `METAGENO`.`OPERON`      `o` ) 
         join `METAGENO`.`OPERON_SITE` `os`) 
         join `METAGENO`.`SITE`        `s` ) 
      
        where( 
              (`o` .`id`     = `g` .`Operon`) 
           and(`os`.`operon` = `o` .`id`    ) 
           and(`s` .`id`     = `os`.`site`   
           and(`s` .`Score`  = (select max(`s2`.`Score`) 
                                    from(`METAGENO`.`SITE` `s2`) 
                                    where(`s2`.`id` = `os`.`site`))))) GROUP BY `o`.`id`

Additional Comment:

Originally Posted by OS-Wiz

Your select is retrieving only those that match the where clause AND have the single highest score. In other words, if there where five entries for a match to your where clause, the select max(`s`.`Score`) is grabbing just the one of the five matched with the highest score. Can you use GROUP BY `op`.`id` HAVING MAX (`s`.`Score`) in your version of SQL syntax and leave out the select max(`s`.`Score`), just use `s`.`Score`?

I get an error on the operator GROUP with:

select `g`.`id`       AS `ID`,  
       `g`.`Name`     AS `GENE`, 
       `o`.`id`       AS `OPID`, 
       `os`.`site`    AS `BSID`, 
       `s`.`Sequence` AS `SITE`, 
       `s`.`Score`    AS `SCORE` 
 
    from((((  
              `METAGENO`.`GENE`        `g` ) 
         join `METAGENO`.`OPERON`      `o` ) 
         join `METAGENO`.`OPERON_SITE` `os`) 
         join `METAGENO`.`SITE`        `s` ) 
      
        where( 
              (`o` .`id`     = `g` .`Operon`) 
           and(`os`.`operon` = `o` .`id`    ) 
           and(`s` .`id`     = `os`.`site`  ) GROUP BY `o`.`id` HAVING MAX(`s`.`Score`)

Additional Comment:

Forgot a paren.

[OS-Wiz]

That should have solved your issue. yes?

[zlander79]

Originally Posted by OS-Wiz

That should have solved your issue. yes?

Yeah, it works. Performance wise is there an advantage of one over the other?

[OS-Wiz]

Originally Posted by zlander79

Yeah, it works. Performance wise is there an advantage of one over the other?

I can only tell you that in Oracle, using the HAVING clause will perform better. Sub-Selects require extra work and a nested-loop join internally. If you have something similar to Oracle's EXPLAIN query, run it and it will show you which would be better for you.

[zlander79]

I keep getting a DDL Statement Error on this one:

CREATE NEW ALGORITHM=UNDEFINED VIEW `METAGENO`.`COMP_REPORT_TEST` AS
 
SELECT 
 
#Table: GENE 
#Symbol: g 
#Column         Output Name 
`g`.`id`     AS `MH_gene_id`, 
`g`.`Name`   AS `MH_name`, 
`g`.`Operon` AS `Operon`, 
 
#Table: FILE 
#Symbol: f 
#Column       Output Name 
`f`.`Path` AS `MH_file`, 
 
#Table: SCAFFOLD 
#Symbol: sc 
`sc`.`Name` AS `MH_scaffold`, 
 
#Table: OPERON 
#Symbol: o 
#Column             Output Name 
`o`.`prime_gene` AS `Operon_Prime_Gene`, 
 
#Table: OPERON SITE 
#Symbol: os 
#Column             Output Name 
`os`.`site`      AS `Site`, 
`os`.`site_type` AS `Site_Type`, 
 
#Table: SITE 
#Symbol: s 
#Column           Output Name 
`s`.`Score`    AS `Site_Score`, 
`s`.`Sequence` AS `Site_Sequence`,  
 
#Table: MOTIF 
#Symbol: m 
#Column       Output Name 
`m`.`name` AS `Motif`, 
 
#Table: GENE_COGPROT 
#Symbol: gcp 
#Column                Output Name 
`gcp`.`eval_coef`  AS `E_coef`, 
`gcp`.`eval_power` AS `E_power`, 
 
#Table: REF_GENE_INFO 
#Symbol: rgi 
#Column                Output Name 
`rgi`.`gi`          AS `NCBI_gi`, 
`rgi`.`accession`   AS `NCBI_Accession`, 
`rgi`.`description` AS `NCBI_Description`, 
`rgi`.`product`     AS `NCBI_Product`, 
 
#Table: COGPROT_COG 
#Symbol: cpc 
#Column             Output Name 
`cpc`.`cog`     AS `COG`, 
`cpc`.`cogprot` AS `COG_Protein`, 
`cpc`.`cog_org` AS `COG_Protein_organism`, 
 
#Table: COG 
#Symbol: c 
#Column       Output Name  
`c`.`Name` AS `COG_Description`, 
 
#Table: COG_FUNCTIONAL_GROUP 
#Symbol: cfg 
#Column         Output Name 
`cfg`.`id`   AS `COG_Group`, 
`cfg`.`Desc` AS `COG_Group_Description`, 
 
#Table: COG_ORG 
#Symbol: co 
#Column         Output Name 
`co`.`taxid` AS `Tax_id`, 
`co`.`name`  AS `Organism_Name` 
 
 
from ((((((((((((( 
          `GENE`                 `g`   
     join `FILE`                 `f`  )  
     join `SCAFFOLD`             `sc` )  
     join `OPERON`               `o`  )  
     join `OPERON_SITE`          `os` )  
     join `SITE`                 `s`  )  
     join `MOTIF`                `m`  )  
     join `GENE_COGPROT`         `gcp`)  
     join `COGPROT_COG`          `cpc`)  
     join `COG`                  `c`  )  
     join `COG_GROUP`            `cg` )  
     join `COG_FUNCTIONAL_GROUP` `cfg`)  
     join `REF_GENE_INFO`        `rgi`)  
     join `COG_ORG`              `co` ) 
 
     where ( 
              (`g`  . `Operon`   = `o`  . `id`     )  
          and (`o`  . `File`     = `f`  . `id`     )  
          and (`o`  . `Scaffold` = `sc` . `id`     )  
          and (`o`  . `id`       = `os` . `operon` )  
          and (`os` . `site`     = `s`  . `id`     )  
          and (`s`  . `Motif`    = `m`  . `id`     )  
          and (`g`  . `id`       = `gcp`. `gene`   )  
          and (`gcp`. `cogprot`  = `cpc`. `gi`     )  
          and (`cpc`. `gi`       = `rgi`. `gi`     )  
          and (`cpc`. `cog`      = `c`  . `id`     )  
          and (`cpc`. `cog`      = `c`  . `id`     )  
          and (`c`  . `id`       = `cg` . `COG`    )  
          and (`cg` . `GROUP`    = `cfg`. `id`     )  
          and (`cpc`. `cog_org`  = `co` . `cog_org`)) 
   
     GROUP BY `o`.`id` HAVING MAX(`s`.`Score`)

[OS-Wiz]

Sorry, can't help, not familiar with that version of SQL.

[sysigy]

What version of SQL are you using

I think there is a syntax error with your HAVING clause... I don't recognise the version of SQL you're using but if it was M$ SQL then the HAVING would need to be a logical test like:

HAVING [SOMETHING] = MAX('s'.'score')

That's just a guess because in M$ SQL, HAVING MAX('s'.'score') would cause a syntax error as there is no test being applied to the result of MAX()

[zlander79]

MySQL, whatever the latest version is. The thing is, if I take out the CREATE VIEW thing, and run the SQL as a query, I have no issues. As soon as I attempt to engineer a view, I get problems. Of course, this being MySQL Workbench there is nothing in the way of sytax/error highlighting, it just says something is wrong.

[OS-Wiz]

Can you use the simple form of create view:

Create or replace view `METAGENO`.`COMP_REPORT_TEST` AS
select `g`.`id`       AS `ID`,  
       `g`.`Name`     AS `GENE`, 
       `o`.`id`       AS `OPID`, 
       `os`.`site`    AS `BSID`, 
       `s`.`Sequence` AS `SITE`, 
       `s`.`Score`    AS `SCORE` 
    from((((  
              `METAGENO`.`GENE`        `g` ) 
         join `METAGENO`.`OPERON`      `o` ) 
         join `METAGENO`.`OPERON_SITE` `os`) 
         join `METAGENO`.`SITE`        `s` ) 
        where( 
              (`o` .`id`     = `g` .`Operon`) 
           and(`os`.`operon` = `o` .`id`    ) 
           and(`s` .`id`     = `os`.`site`  ) 
        GROUP BY `o`.`id` HAVING MAX(`s`.`Score`)

I used this reference: http://dev.mysql.com/doc/refman/5.0/en/create-view.html

[jeremy]

Since the problem is probably in the view syntax (I've never used it)..
My hunch though if you're getting DDL errors is probably something related to the DDL fields. (I know im captain obvious lol)

This might not help you at all but I tried to clean the original query (just my preference, its easier to debug imo (less parens and backticks)..

Also capitalized field names scare me

SELECT g.id as ID,
g.Name as GENE,
o.id as OPID,
os.site as BSID,
s.Sequence as SITE,
s.Score as SCORE
FROM METAGENO.GENE g
JOIN METAGENO.OPERON o ON o.id = g.Operon
JOIN METAGENO.OPERTON_SITE os ON os.operon = o.id
JOIN METAGENO.SITE s ON s.id = os.site
GROUP BY o.id
HAVING MAX(s.Score);