[pg300]

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.

[r937]

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'

[pg300]

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!

[pg300]

For instance, I'm trying to learn your method of selecting from a select so I'm starting with this:

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

[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.

[pg300]

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!

[pg300]

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.

[r937]

Originally Posted by pg300

I'm trying hard to learn this.

which part are you having trouble understanding?

[pg300]

Like I'm trying to apply that to this query:

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:

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!

[r937]

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

[pg300]

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

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:

  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! :

//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!

//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.

[pg300]

Oh my gosh I got it!!! With a slight problem though

So the query is this:

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:

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

[pg300]

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:

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!

[r937]

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 --

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

[pg300]

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?

[r937]

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