September 24th, 2011, 11:00 PM
-
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.
September 25th, 2011, 04:54 AM
-
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
September 29th, 2011, 11:21 PM
-
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!
September 30th, 2011, 12:13 AM
-
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.
October 1st, 2011, 10:19 AM
-
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!
October 13th, 2011, 03:41 AM
-
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.
October 13th, 2011, 06:23 AM
-
Originally Posted by pg300
I'm trying hard to learn this.
which part are you having trouble understanding?
October 14th, 2011, 12:56 AM
-
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!
October 14th, 2011, 03:10 AM
-
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
October 14th, 2011, 06:28 AM
-
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.
October 14th, 2011, 07:36 AM
-
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
October 15th, 2011, 10:37 PM
-
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!
October 15th, 2011, 11:18 PM
-
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
October 16th, 2011, 12:21 PM
-
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?
October 16th, 2011, 02:59 PM
-
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