Page 1 of 2 12 Last
  • Jump to page:
    #1
  1. No Profile Picture
    Contributing User
    Devshed Newbie (0 - 499 posts)

    Join Date
    Aug 2006
    Posts
    47
    Rep Power
    8

    Select first/unique value but have multiple columns


    Hi all,
    I have been struggling so much with this query.
    Basically I want to run this query:
    SELECT m_lot.operationname, m_lot.parentcontainername, m_lot.lastactivitydate
    FROM EQDB_Insite.dbo.m_lot m_lot
    WHERE (m_lot.parentcontainername Like 'E%') AND (m_lot.operationname Like 'MZ%') AND (m_lot.status='Open') ORDER BY m_lot.lastactivitydate DESC

    and then get unique m_lot.parentcontainername (by unique i mean the one with the biggest/mostRecent lastactivitydate.

    I tried this:
    SELECT m_lot.operationname, m_lot.parentcontainername, MAX(m_lot.lastactivitydate)
    FROM EQDB_Insite.dbo.m_lot m_lot GROUP BY m_lot.parentcontainername HAVING (m_lot.parentcontainername Like 'E%') AND (m_lot.operationname Like 'MZ%') AND (m_lot.status='Open')

    but then it says i have to use aggregat functions or put the other col names in the group by clause, if i put the other column names in the group by clause i then get multiple values in the parentcontainername field.

    Please help

    I also don't know what to classify this sql as, it definitely isn't mysql, is there a way i can figure out? the aggregate functions of first and last dont work.
  2. #2
  3. SQL Consultant
    Devshed Supreme Being (6500+ posts)

    Join Date
    Feb 2003
    Location
    Toronto Canada
    Posts
    27,150
    Rep Power
    4274
    Code:
    SELECT t.parentcontainername
         , t.operationname
         , t.lastactivitydate
      FROM ( SELECT parentcontainername
                  , MAX(lastactivitydate) AS last_activity
               FROM EQDB_Insite.dbo.m_lot
              WHERE parentcontainername LIKE 'E%'
                AND operationname LIKE 'MZ%'
                AND status = 'Open'
             GROUP
                 BY parentcontainername ) AS m
    INNER
      JOIN EQDB_Insite.dbo.m_lot AS t
        ON t.parentcontainername = m.parentcontainername
       AND t.lastactivitydate = m.last_activity
       AND t.operationname LIKE 'MZ%'
       AND t.status = 'Open'

    Comments on this post

    • pg300 agrees : thank you so so so much!!! please teach how to be like you
    rudy.ca | @rudydotca
    Buy my SitePoint book: Simply SQL
  4. #3
  5. No Profile Picture
    Contributing User
    Devshed Newbie (0 - 499 posts)

    Join Date
    Aug 2006
    Posts
    47
    Rep Power
    8
    Oh my gosh thank you so so much! I didn't test it out but thanks for the prompt prompt reply!!!
    I will so try to learn what each statement does. Is there anywhere you can point me to, so i can learn to do stuff like that please?

    Thanks so much!
  6. #4
  7. No Profile Picture
    Contributing User
    Devshed Newbie (0 - 499 posts)

    Join Date
    Aug 2006
    Posts
    47
    Rep Power
    8
    For instance, I'm trying to learn your method of selecting from a select so I'm starting with this:

    Code:
    SELECT DISTINCT m.containername FROM ( SELECT containername FROM EQDB_Insite.dbo.m_lot WHERE containername Like 'E%' AND status = 'Open' ORDER BY lastactivity DESC ) AS m
    But I get an error saying
    Code:
    [Microsoft][ODBC SQL Server Driver][SQL Server]The ORDER BY clause is invalid in views, inline functions, derived tables, and subqueries, unless TOP is also specified.
  8. #5
  9. No Profile Picture
    Contributing User
    Devshed Newbie (0 - 499 posts)

    Join Date
    Aug 2006
    Posts
    47
    Rep Power
    8
    Hi again friend. Can you please break down why you did each part in there for me. I can't understand what made us require to use all those pieces.

    Thanks so much!
  10. #6
  11. No Profile Picture
    Contributing User
    Devshed Newbie (0 - 499 posts)

    Join Date
    Aug 2006
    Posts
    47
    Rep Power
    8
    Hi again friend, if you please have some time to explain each part of the query that would be so so nice. I'm trying hard to learn this.
  12. #7
  13. SQL Consultant
    Devshed Supreme Being (6500+ posts)

    Join Date
    Feb 2003
    Location
    Toronto Canada
    Posts
    27,150
    Rep Power
    4274
    Originally Posted by pg300
    I'm trying hard to learn this.
    which part are you having trouble understanding?
    rudy.ca | @rudydotca
    Buy my SitePoint book: Simply SQL
  14. #8
  15. No Profile Picture
    Contributing User
    Devshed Newbie (0 - 499 posts)

    Join Date
    Aug 2006
    Posts
    47
    Rep Power
    8
    Like I'm trying to apply that to this query:
    Code:
    SELECT m.containername, m.productname, x.AverageAlPerc, c.txndate AS EPIDATE, x.txndate AS XRAYDATE FROM dbo.m_lot m, dbo.DC_0300MCOM x, dbo.DC_0200CUST c WHERE x.containername=m.containername AND c.containername=m.containername AND m.productname='21144370' AND m.containername LIKE 'X%' ORDER BY EPIDATE ASC
    But that gives me all data. What I'm trying to do is the most recent 30 points. So I put in a "TOP 30" after the select. But there are duplicate entries in the x table and i want to select the one with the MAX XRAYDATE in the x table but I don't necesarily want the MAX AverageAlPerc value, as this value can be less at a later date.


    I came up with this:
    Code:
    SELECT TOP 30 m.containername, x.AverageAlPerc, x.txndate FROM ( SELECT containername, Max(txndate) , Max(AverageAlPerc) 
    FROM EQDB_Insite.dbo.DC_0300MCOM
    WHERE containername LIKE 'X%'
    GROUP BY containername ) As x
    INNER JOIN EQDB_Insite.dbo.m_lot As m
    ON m.containername = x.containername AND m.productname = '21144370'
    But then I get invalid char near where.

    Thanks so much sir!
  16. #9
  17. SQL Consultant
    Devshed Supreme Being (6500+ posts)

    Join Date
    Feb 2003
    Location
    Toronto Canada
    Posts
    27,150
    Rep Power
    4274
    Code:
    SELECT TOP 30 
         , m.containername
         , m.AverageAlPerc
         , m.txndate 
      FROM ( SELECT containername
                  , MAX(txndate) AS maxtxndate 
               FROM EQDB_Insite.dbo.DC_0300MCOM
              WHERE containername LIKE 'X%'
             GROUP 
                 BY containername ) As x
    INNER 
      JOIN EQDB_Insite.dbo.m_lot As m
        ON m.containername = x.containername 
       AND m.txndate       = x.maxtxndate 
       AND m.productname = '21144370'
    ORDER
        BY m.txndate ASC

    Comments on this post

    • pg300 agrees : Thanks so much for attempting to help me again! :)
    rudy.ca | @rudydotca
    Buy my SitePoint book: Simply SQL
  18. #10
  19. No Profile Picture
    Contributing User
    Devshed Newbie (0 - 499 posts)

    Join Date
    Aug 2006
    Posts
    47
    Rep Power
    8
    Oh my gosh I struggled with that for ever! Thanks so much. With these 2 examples I think i can learn a lot!

    I tried plugging it in and it says
    Code:
    Could not add the table '('
    Also we're missing the third table in this query. I also forgot to include in my attempted query i pasted second (above). The first query i pasted works though, it just gives us duplicate values for containername with a different AverageAlPerc and older date. I just want the combination with the latest date.

    Also the field AverageAlPerc is actually a field in EQDB_Insite.dbo.DC_0300MCOM table (the x table). There could be multiple entries and I want to select the one that corresponds to the max txndate in this table (which is the most recent/latest date right?).

    Thanks so much for your help. Also could you please explain another thing:
    Code:
      FROM ( SELECT containername
                  , MAX(txndate) AS maxtxndate 
               FROM EQDB_Insite.dbo.DC_0300MCOM
              WHERE containername LIKE 'X%'
             GROUP 
                 BY containername ) As x
    in that part I noticed you had to go " AS maxtxndate ", is that necessary? Is it because there is a txndate field in all 3 tables?
    Thanks so much!



    edit:
    So i'm working more with the great stuff you taught me.
    So I succesfully got the this is the first step! :
    Code:
    //purpose select only the latest AverageAlPerc per container
    //logic here:
    //first select the max date corresponding with the grouped container name
    //then inner join this with the same table but now where containername and txndate are of the first select we did
    SELECT x1.containername, x1.AverageAlPerc, x1.txndate
      FROM ( SELECT containername
                  , MAX(txndate) AS LatestXrayDate
               FROM EQDB_Insite.dbo.DC_0300MCOM
              WHERE containername LIKE 'X%'
             GROUP BY containername) AS x0
    INNER
      JOIN EQDB_Insite.dbo.DC_0300MCOM AS x1
        ON x1.containername = x0.containername AND x1.txndate = x0.LatestXrayDate
    Now i have to inner join that with m_lot table where product = 21144370, i'll try that now.

    edit 2:
    I think i got it to the second step now!
    Code:
    //purpose select from the latest only xray data the containernames that are of productname 21144370
    SELECT x2.containername, x2.AverageAlPerc, x2.txndate FROM ( SELECT x1.containername, x1.AverageAlPerc, x1.txndate
      FROM ( SELECT containername
                  , MAX(txndate) AS LatestXrayDate
               FROM EQDB_Insite.dbo.DC_0300MCOM
              WHERE containername LIKE 'X%'
             GROUP BY containername) AS x0
    INNER
      JOIN EQDB_Insite.dbo.DC_0300MCOM AS x1
        ON x1.containername = x0.containername AND x1.txndate = x0.LatestXrayDate ) As x2
    INNER JOIN EQDB_Insite.dbo.m_lot AS m ON x2.containername = m.containername AND m.productname='21144370'
    now I need to try and order it by txndate from a third table EQDB_Insite.dbo.DC_0200CUST

    I'll try now.
  20. #11
  21. No Profile Picture
    Contributing User
    Devshed Newbie (0 - 499 posts)

    Join Date
    Aug 2006
    Posts
    47
    Rep Power
    8
    Oh my gosh I got it!!! With a slight problem though

    So the query is this:
    Code:
    SELECT x2.containername, x2.AverageAlPerc, x2.txndate as XrayDate, c.txndate as EpiDate FROM ( SELECT x1.containername, x1.AverageAlPerc, x1.txndate
      FROM ( SELECT containername
                  , MAX(txndate) AS LatestXrayDate
               FROM EQDB_Insite.dbo.DC_0300MCOM
              WHERE containername LIKE 'X%'
             GROUP BY containername) AS x0
    INNER
      JOIN EQDB_Insite.dbo.DC_0300MCOM AS x1
        ON x1.containername = x0.containername AND x1.txndate = x0.LatestXrayDate ) As x2
    INNER JOIN EQDB_Insite.dbo.m_lot AS m ON x2.containername = m.containername AND m.productname='21144370'
    INNER JOIN EQDB_Insite.dbo.DC_0200CUST AS c ON x2.containername = c.containername
    So the logic here is the first select x0 gets the latest date corresponding to the container (grouped by container)
    The next (x1) joins the AverageAlPerc to container and date pair.
    The next x2 then filters out the data unless it is found in the m_lot table that the x1.container has productName of 21144370
    AND this x2 query also joins the txndate from the DC_0200CUST table BUT the problem is here. The DC_0200CUST table has two entries for container value of "X2913", so the data is repeated and i for each EpiDate. I want to only join the latest date from this cust table, how could i do that?



    edit: I cleaned up the query above so there is no need for x2. WOW thanks so much sir I learned an incredible amount from you!!

    cleaned up query:
    Code:
    SELECT x1.containername, x1.AverageAlPerc, x1.txndate as XrayDate, c.txndate as EpiDate
      FROM ( SELECT containername
                  , MAX(txndate) AS LatestXrayDate
               FROM EQDB_Insite.dbo.DC_0300MCOM
              WHERE containername LIKE 'X%'
             GROUP BY containername) AS x0
    INNER JOIN EQDB_Insite.dbo.DC_0300MCOM AS x1 ON x1.containername = x0.containername AND x1.txndate = x0.LatestXrayDate
    INNER JOIN EQDB_Insite.dbo.m_lot AS m ON x1.containername = m.containername AND m.productname='21144370'
    INNER JOIN EQDB_Insite.dbo.DC_0200CUST AS c ON x1.containername = c.containername
  22. #12
  23. No Profile Picture
    Contributing User
    Devshed Newbie (0 - 499 posts)

    Join Date
    Aug 2006
    Posts
    47
    Rep Power
    8
    Hi I got it! I used the same duplicate removal technique within the inner join. Can you please tell me if this is the best way to write this query:
    Code:
    SELECT *
    FROM   (SELECT TOP 1000 x1.containername,
                            x1.averagealperc,
                            x1.txndate AS xraydate,
                            c.epidate
            FROM   (SELECT containername,
                           MAX(txndate) AS latestxraydate
                    FROM   eqdb_insite.dbo.dc_0300mcom
                    WHERE  containername LIKE 'X%'
                    GROUP  BY containername) AS x0
                   INNER JOIN eqdb_insite.dbo.dc_0300mcom AS x1
                     ON x1.containername = x0.containername
                        AND x1.txndate = x0.latestxraydate
                   INNER JOIN eqdb_insite.dbo.m_lot AS m
                     ON x1.containername = m.containername
                        AND m.productname = '21144370'
                   INNER JOIN (SELECT containername,
                                      MAX(txndate) AS epidate
                               FROM   eqdb_insite.dbo.dc_0200cust
                               GROUP  BY containername) AS c
                     ON x1.containername = c.containername
            ORDER  BY c.epidate DESC) AS flipthis
    ORDER  BY flipthis.epidate ASC
    Thanks so much!
  24. #13
  25. SQL Consultant
    Devshed Supreme Being (6500+ posts)

    Join Date
    Feb 2003
    Location
    Toronto Canada
    Posts
    27,150
    Rep Power
    4274
    Originally Posted by pg300
    Can you please tell me if this is the best way to write this query:
    no, i don't think so... for one thing you shouldn't have an ORDER BY inside the subquery

    try this --
    Code:
    SELECT TOP 1000 
           x1.containername
         , x1.averagealperc
         , x1.txndate AS xraydate
         , c.epidate
      FROM ( SELECT containername
                  , MAX(txndate) AS latestxraydate
               FROM eqdb_insite.dbo.dc_0300mcom
              WHERE containername LIKE 'X%'
             GROUP
                 BY containername ) AS x0
    INNER 
      JOIN eqdb_insite.dbo.dc_0300mcom AS x1
        ON x1.containername = x0.containername
       AND x1.txndate = x0.latestxraydate
    INNER 
      JOIN eqdb_insite.dbo.m_lot AS m
        ON x1.containername = m.containername
       AND m.productname = '21144370'
    INNER 
      JOIN ( SELECT containername
                  , MAX(txndate) AS epidate
               FROM eqdb_insite.dbo.dc_0200cust
             GROUP  
                 BY containername ) AS c
        ON x1.containername = c.containername
    ORDER  
        BY c.epidate ASC
    rudy.ca | @rudydotca
    Buy my SitePoint book: Simply SQL
  26. #14
  27. No Profile Picture
    Contributing User
    Devshed Newbie (0 - 499 posts)

    Join Date
    Aug 2006
    Posts
    47
    Rep Power
    8
    Well I had to have the order by inside the thing because I wanted to select the bottom 100 rows. I tried SELECT BOTTOM 100 but it doesn't work. So I ordered it in the oppoiste direction so the bottom 100 are now the top 100 in ascending order if you look from bottom to top. Then once I get those 100 I flip it so I get that bottm 100 rows in descending order.

    Is there a better way to do that? Is there something like bottom?
  28. #15
  29. SQL Consultant
    Devshed Supreme Being (6500+ posts)

    Join Date
    Feb 2003
    Location
    Toronto Canada
    Posts
    27,150
    Rep Power
    4274
    Originally Posted by pg300
    Is there a better way to do that?
    depends on your definition of "better"

    i would retrieve the 100 rows as directly as i can, and if this means i print them in reverse sequence, my front end application can do that easily

    rudy.ca | @rudydotca
    Buy my SitePoint book: Simply SQL
Page 1 of 2 12 Last
  • Jump to page:

IMN logo majestic logo threadwatch logo seochat tools logo