1. Get rid of all advertisements and get unlimited access to documents by upgrading to Premium Membership. Upgrade to Premium Now and also get a Premium Badge!

What is wrong with my query?

Discussion in 'General' started by Bnat, Mar 18, 2014.

  1. Bnat

    Bnat Active Member

    Messages:
    4
    Likes Received:
    0
    Trophy Points:
    55
    Table:


    Sample Table Data
    DriverID Fname Lname DOB Disposition
    1 John Doe 19651025 Not Guilty
    2 Frank Wright 19770115 Guilty
    2 Frank Wright 19770115 Guilty
    3 Ed Jones 19810604 Guilty
    4 Mary Jones 19800730 Not Guilty
    4 Mary Jones 19800730 Guilty
    5 Larry Able 19771201 Not Guilty


    Based on the sample table data above write a query using a JOIN that will list the names of people that ONLY have a Guilty disposition and do NOT share a Last Name with anyone else.
    (note: the sample data is just for reference. you cannot use a persons name explicitely in the query, i.e. and Fname = ‘name’)

    My query works but it doesn't return something when it should:

    select a.fname,a.lname,a.disposition from test a
    inner join (select lname,COUNT(*) as Frequency
    from test
    Group by lname having COUNT(*)=1) b on b.lname = a.lname
    where a.disposition ='Guilty';
     
  2. krasnoslobodtsev_si

    krasnoslobodtsev_si Forum Genius

    Messages:
    626
    Likes Received:
    129
    Trophy Points:
    810
    Location:
    Russian Federation
    Tell, Bnat, you study sql?

    I don't explained to you as the query worked.
    Understand independently.
    See links to documentation

    http://docs.oracle.com/cd/E11882_01/server.112/e26088/functions004.htm#SQLRF06174
    http://www.oracle-base.com/articles/misc/analytic-functions.php


    Variants of queries :

    Code (SQL):

    WITH
    you_data AS
    (
    SELECT 1 DriverID , 'John' fname, 'Doe' lname , 19651025  DOB ,'Guilty' Disposition FROM dual UNION ALL    
    SELECT 2 ,'Frank' ,'Wright', 19770115 ,'Guilty' FROM dual UNION ALL
    SELECT 2 ,'Frank','Wright', 19770115 ,'Not Guilty' FROM dual UNION ALL
    SELECT 3 ,'Ed', 'Jones', 19810604 ,'Guilty' FROM dual UNION ALL
    SELECT 4, 'Mary', 'Jones', 19800730 , 'Not Guilty' FROM dual UNION ALL
    SELECT 4 ,'Mary','Jones', 19800730 ,'Guilty' FROM dual  UNION ALL
    SELECT 5 ,'Larry',' Able', 19771201,'Guilty' FROM dual
    )
    ,resultset AS(
    SELECT
          d.driverid,d.fname,d.lname,d.disposition,
          COUNT(dob) OVER (partition BY lname) cnt    
    FROM you_data d
    )
    SELECT * FROM resultset
    WHERE cnt = 1
    AND Disposition  = 'Guilty';


    SQL>



      DRIVERID FNAME LNAME DISPOSITION        CNT
    ---------- -----          ----------  -----------          ----------
             5     Larry   Able     Guilty                  1
             1     John    Doe     Guilty                  1
     


    Code (SQL):

    WITH
    you_data AS
    (
    SELECT 1 DriverID , 'John' fname, 'Doe' lname , 19651025  DOB ,'Guilty' Disposition FROM dual UNION ALL    
    SELECT 2 ,'Frank' ,'Wright', 19770115 ,'Guilty' FROM dual UNION ALL
    SELECT 2 ,'Frank','Wright', 19770115 ,'Guilty' FROM dual UNION ALL
    SELECT 3 ,'Ed', 'Jones', 19810604 ,'Guilty' FROM dual UNION ALL
    SELECT 4, 'Mary', 'Jones', 19800730 , 'Not Guilty' FROM dual UNION ALL
    SELECT 4 ,'Mary','Jones', 19800730 ,'Guilty' FROM dual  UNION ALL
    SELECT 5 ,'Larry',' Able', 19771201,'Guilty' FROM dual
    )

    SELECT
          MAX(driverid) keep (dense_rank FIRST ORDER BY d.lname) driverid,
          MAX(fname) keep (dense_rank FIRST ORDER BY d.lname) fname,
          d.lname,
          MAX(d.disposition ) keep (dense_rank FIRST ORDER BY d.lname) disposition
    FROM you_data d

    GROUP BY  d.lname      

    HAVING  MAX(d.disposition ) keep (dense_rank FIRST ORDER BY d.lname)  = 'Guilty'

    AND
      COUNT(*) =1 ;


    SQL>

      DRIVERID FNAME LNAME  DISPOSITION
    ---------- ----- ------ -----------
             5 Larry  Able  Guilty      
             1 John  Doe    Guilty