subquery help

[travismp]

I am trying to write a complex query filtering data from a large table and I am having problems. I am going to work on this in baby steps so I can figure out how to do this correctly. Here is my first subquery.

SELECT [tbl_FINAL].CompName, [tbl_FINAL].CompLocation
FROM tbl_FINAL
WHERE (SELECT tbl_FINAL.CompName                            
FROM tbl_FINAL   
WHERE tbl_FINAL.CompName="ABC COMPANY");

OK the way I read this is sql statement is like this: The result set should show me each record listed for ABC Company and the Location of that company.

So If I had "ABC Company" listed in that table 13 times it should show me the 13 records and the CompLocation of each.

When I run just this I get a "At most one record can be returned by this subquery" error. Is my code wrong? This is my first attempt at a subquery.

 

[pbaldy]

The subquery makes no sense at this point, but I assume that's because you're testing (there's no purpose for it). Try:

SELECT [tbl_FINAL].CompName, [tbl_FINAL].CompLocation
FROM tbl_FINAL
WHERE CompName = (SELECT tbl_FINAL.CompName
FROM tbl_FINAL
WHERE tbl_FINAL.CompName="ABC COMPANY");

 

[travismp]

Ya I know it makes no sense. I thank you for making an attempt. Maybe instead of trying to build it from scratch all jacked up I should ask my question a little better.

I have a table with these fields:

AccID (primary key)
CompName
CompLocation
CompEmail
CompPhone
CompOrderType
CompOrderDate

My table has 100,000+ records. So [ComName] "ABC Company" may have 125 of those records. [CompLocation] "Dallas TX" can have 55 of those 125.

I am trying to find a way write a query that will display all of the columns for the MAX [CompOrderDate] for each location. So out of those 55 for "Dallas TX" I want to see the most recent order and filter out the other 54 orders. I need to see all of the columns for that record. I need to see the most recent order for each location for each company.

can this be done?

 

[ByteMyzer]

You might try the following (substitute highlighted text with actual table/field names):

SELECT T1.*
FROM [b][i]tbl_FINAL[/i][/b] T1
WHERE T1.[b][i]CompOrderDate[/i][/b] =
   (SELECT MAX(T2.[b][i]CompOrderDate[/i][/b])
    FROM [b][i]tbl_FINAL[/i][/b] T2
    WHERE T2.[b][i]CompName[/i][/b] = T1.[b][i]CompName[/i][/b]
    AND T2.[b][i]CompLocation[/i][/b] = T1.[b][i]CompLocation[/i][/b]);

 

[pbaldy]

You might see if this adapts to your needs as well:

http://www.baldyweb.com/LastValue.htm

 

[travismp]

I think that is going to work. I added one more condition.

SELECT T1.*
FROM tbl_FINAL T1
WHERE T1.CompOrderDate =
   (SELECT MAX(T2.CompOrderDate)
    FROM tbl_FINAL T2
    WHERE T2.CompName = T1.CompName
    AND T2.CompOrderType = "ONLINE"
    AND T2.CompLocation = T1.CompLocation);

This way it will only show orders placed online. Is there anything wrong with the way I listed this?

Also one small hickup but not a huge deal. If a specific location placed two orders on the same day they are both showing up. Is there any way to stop that so only one of the two are there? I can work around that if it not an easy fix. Thanks.

Otherwise that was a GREAT FIX. Thanks.

 

[ByteMyzer]

travismp,

Is the field AccID an AutoNumber field?

 

[travismp]

Yes it is.

 

[ByteMyzer]

You might see if this adapts to your needs as well:

http://www.baldyweb.com/LastValue.htm

Yes, pbaldy, if travismp does not wish to use a subquery, that is another useful method.

 

[ByteMyzer]

Yes it is.

In that case, you might try this:

SELECT T1.*
FROM tbl_FINAL T1
WHERE T1.AccID =
   (SELECT MAX(T2.AccID)
    FROM tbl_FINAL T2
    WHERE T2.CompName = T1.CompName
    AND T2.CompOrderType = "ONLINE"
    AND T2.CompLocation = T1.CompLocation);