Say you have two tables (we'll call them WasherSales and NutSales)

The organization of each table is like this:

SaleID......SaleDate......CustomerName......NumberOrdered


I want a query to return me the a data set which has the following columns

1) CustomerName (no duplicates, each customer should only have one row)
2) Most recent SaleDate for Washers for each particular customer
3) The NumberOrdered associated with that most recent SaleDate of Washers
4) Mose recent SaleDate for Nuts
5) The NumberOrdered associated with that most recent SaleDate of Nuts


I've tried a few different things using aggregate functions and subqueries etc but I can't make it work. Oh and this is a MSSQL system.


Any ideas?

[Edited on October 25, 2006 at 3:43 PM. Reason : MSSQL]

10/25/2006 3:42:32 PM

if you mean, only one row displayed on the page, how do you expect customername to only return one row if a customer has made a purchase more than once? or are you looking to add up all the results per customer?

for most recent saledate ... when you request the query tell it to order by saledate descending, and also that you only want 1 result.

when you pull the query it will automatically have the numberordered field information for the specific saledate, that is, unless you don't choose to request it in the query either specifically or by selecting all fields.

you doing this entirely in mssql, or are you using something like asp/php too? being more specific and/or posting your script would be a big help in helping you.

10/25/2006 4:23:50 PM

first of all you need to merge the two tables and just add a column "Sale type", that ridiculous having two tables with identical information.

second, ill assume the saledate is in the form of a timestamp (if it isnt, it should be)

SELECT DISTINCT CustomerName, SaleDate, NumberOrdered, SaleType FROM OrderTable ORDER BY SaleDate DESC;

Should give you what you want. The last sale, its number ordered, customer name and saletype for each and only the first one that matches.

[Edited on October 25, 2006 at 5:20 PM. Reason : its been a while since i've touched SQL though, so YMMV]

10/25/2006 5:20:20 PM

Quote :

"how do you expect customername to only return one row if a customer has made a purchase more than once"

Quote :

"no duplicates, each customer should only have one row"

Its like a group by. For each customer, I want to know information about their most recent purchase of washers and their most recent purchase of nuts. The resulting data set should have one row for each customer.

Quote :

"that ridiculous having two tables with identical information."

This is a ficticious (and overly simplistic) setup...my actual use for this approach involves much more complicted tables (30+) but the results I need are in line with what I detailed in the first post and the method should translate perfectly to my application.

Quote :

"SELECT DISTINCT CustomerName, SaleDate, NumberOrdered, SaleType FROM OrderTable ORDER BY SaleDate DESC;"

I dont think that would work. I need a single line for each customer that has the following info:

Quote :

"1) CustomerName (no duplicates, each customer should only have one row) 2) Most recent SaleDate for Washers for each particular customer 3) The NumberOrdered associated with that most recent SaleDate of Washers 4) Mose recent SaleDate for Nuts 5) The NumberOrdered associated with that most recent SaleDate of Nuts "

CustomerName.......RecentWasherSaleDate.......WasherNumber.......RecentNutsSaleDate......NutsNumber


I could do this by importing data into excel then using a vlookup function to give the end result but I would really like to know how to have a single sql script produce it...not so much for this application but more for general knowledge.



[Edited on October 25, 2006 at 5:46 PM. Reason : ]

10/25/2006 5:25:55 PM

in that case, Noen typed the sql code as i described. i was actually wondering myself why you are bothering to use two tables. that implementation causes a headache when you split out some info (purchase type) and not other info (customer data) and then try to relate the two by the duplicated info.

the only thing i'd change from his example is if you want to see the last purchase of a specific product ... add WHERE SaleType = "Washers" (or the same, but with "Nuts") after your FROM statement. now all data can be in the same table. likewise if you just wanted to evaluate the most recent purchase without regard to product, you just leave out the WHERE statement.

i asked about other languages because you could be more flexible with your data manipulation/display then.

*
ok, to combine queries is more complicated, hold on ....

[Edited on October 25, 2006 at 5:41 PM. Reason : edit for your edit]

10/25/2006 5:40:38 PM

ok, just did some research and now i remember, you'll have to run multiple queries, because the DISTINCT argument is not limited to the one field CustomerName. therefore, you would get a distinct return when more than 1 person had purchased 1 washer, or bought something on the same date, etc.

it's going to involve a lot more than a simple sql query, and i'm not anywhere near qualified to give you mssql scripting advice beyond the actual sql commands.

you could try running some UNIONs or JOIN or OUTERJOINs on multiple queries and see what you come up with ... but i'm not hopeful that's going to get you your results.

*
one more thing, generally what you're asking is to create a report, and afaik the language of a database query does not do that. what were you planning on doing with this query beyond the database?

[Edited on October 25, 2006 at 6:08 PM. Reason : ?]

10/25/2006 6:02:09 PM

Select distinct will work still, because you are ordering by saledate and you only want one item anyway.

This is the problem with posting questions like this. Your problem is EXTREMELY simplified and not representative of your actual data. You have 30+ tables of what? 30 products with this kind of data in each table?

If the data is organized this way, even with 1000 tables it seems pretty wasteful if you are duplicating all this information.

But I digress.

Okay to get what you want, this should work, assuming you can use a subquery:

SELECT CustomerName, MAX(RecentWasherSaleDate), WasherNumber, MAX(RecentNutsSaleDate), NutsNumber FROM ((SELECT DISTINCT CustomerName FROM washerTable) INNER JOIN washerTable USING CustomerName) INNER JOIN nutsTable USING CustomerName GROUP BY CustomerName ASC, RecentWasherSaleDate DESC, RecentNutsSaleDate DESC

Should work. But if you have discreet tables for each type, it's going to get enormously complex, which is yet another reason to not only move the tables together, but to also just use the "CustomerName" as an key to a customers table (which im sure it already is) which would remove the need for a subquery, and instead you could just join against that table.

[Edited on October 25, 2006 at 9:04 PM. Reason : .]

10/25/2006 9:04:13 PM

Quote :

"This is the problem with posting questions like this. Your problem is EXTREMELY simplified and not representative of your actual data. You have 30+ tables of what? 30 products with this kind of data in each table?"

As I mentioned in a past thread, I work for a human services agency and one of our services is counseling. Our counseling clients are evaluated many times throughout the year and receive a score. They receive this score on a variety of different "tests" and each one of these different tests has its own table. Each table has 20-50 columns which varies wildly between the different tests. So for each client, and for each different type of "test", I want their most recent test score associated with that test.

ClientName.......RecentTestADate.....RecentTestAScore.......RecentTestBDate.......RecentTestBScore and so on

I picked washer and but sales because its much easier to explain and understand and apply a query to. The approach is the same if we're talking about hardware sales or counseling test scores. I can use this logic beyond this one report though so I would really like to figure it out.

I haven't been using Distinct but its probably a good idea to try. The one thing I worry about is wheter its going to get the correct NutsNumber and WasherNumber associated with the most recent sale...or just the first one it comes across, but I'll give it a try. thanks.

10/26/2006 12:39:35 PM

Quote :

"The one thing I worry about is wheter its going to get the correct NutsNumber and WasherNumber associated with the most recent sale...or just the first one it comes across"

that's why you're sorting by SaleDate DESC ... it'll put the most recent on top.

10/26/2006 12:52:39 PM

Quote :

"SELECT CustomerName, MAX(RecentWasherSaleDate), WasherNumber, MAX(RecentNutsSaleDate), NutsNumber FROM ((SELECT DISTINCT CustomerName FROM washerTable) INNER JOIN washerTable USING CustomerName) INNER JOIN nutsTable USING CustomerName GROUP BY CustomerName ASC, RecentWasherSaleDate DESC, RecentNutsSaleDate DESC"

I tried your query, but the first point is if you're going to use aggregate functions, any item in the select list which is not using an aggregate has to be in the group by expression. so in your query, you would have to group by WasherNumber and NutsNumber.

Here is my query using your logic:

SELECT DISTINCT tt.ClientId,MAX(a.DateCreated),a.gafScore,MAX(b.DateCreated),b.gafScore
FROM (SELECT DISTINCT ClientId
FROM TestA) tt
INNER JOIN TestA a
ON tt.ClientId = a.ClientId
INNER JOIN TestB b
ON tt.ClientId = b.ClientId
GROUP BY tt.ClientId,r.DateCreated,c.DateCreated,r.gafScore,c.gafScore

This query still produces duplicate rows though

10/26/2006 1:17:00 PM

i might try to use views...will try it tomorrow.

[Edited on October 26, 2006 at 2:35 PM. Reason : ]

10/26/2006 2:34:06 PM

Heres how I would do it...

I query to get the a row for each distinct customer. Then go through that result and do a query for most recent washersale (LIMIT 1 at end of the query) and one for the most recent nut sale (same thing), 1 customer at a time. Print that line however you need it, or write it all to an array or whatever you are trying to do with it at that point....

SQL can handle 3x the number of queries no prob.

[Edited on October 26, 2006 at 2:53 PM. Reason : .]

10/26/2006 2:52:50 PM

Quote :

"i might try to use views...will try it tomorrow."

Oh jeeze man. I didn't realize you were using an enterprise level RDBMS. Yea you can setup a couple of views and do this in a matter of minutes. I don't know why I always assume people with SQL questions are running MySQL.

Post the solution when you work it out, and if you need help with views, lemme know what kind of server you are running and I'll see what I can dig out of my butt

10/26/2006 3:38:20 PM

Quote :

"I'll see what I can dig out of my butt"

.... that is a terrible twist on the phrase "pull something out of my butt"

10/26/2006 4:07:37 PM

for anyone curious heres the query i ended up using (modified for ease of understanding)

the trick was getting the most recent information for each client&event independently, then joining my results together.

SELECT * FROM
(SELECT ClientID, DateCreated AS 'TestA Date', Score AS 'TestA Score'
FROM TestA
WHERE ID IN
(SELECT MAX(ID) FROM TestA GROUP BY ClientID))TestA
JOIN
(SELECT ClientID, DateCreated AS 'TestB Date', Score AS 'TestB Score' FROM TestB
WHERE ID IN
(SELECT MAX(ID)
FROM TestB
WHERE DateCreated BETWEEN '2005-07-01' AND '2006-07-01'
GROUP BY ClientID))TestB
ON TestA.ClientID=TestB.ClientID


Heres the query for my washers and nuts example (written by some guy off experts exchange)

SELECT * FROM
(select * from WasherSales
where saleID in (select max(saleID) from WasherSales group by customerID)
)WasherSales
JOIN
(
select * from NutSales
where saleID in (select max(saleID) from NutSales group by customerID)
)NutSales
ON WasherSales.customerID=NutSales.CustomerID


Thanks for everyones comments and ideas.

[Edited on October 27, 2006 at 12:26 PM. Reason : ]

10/27/2006 12:24:13 PM

^expert's exchange, great site, had a subscription for years.

glad you finally got it working!

10/27/2006 12:57:21 PM

^^glad you got a solution, but geeze that makes my brain hurt, 3 level deep subqueries yikes!

10/27/2006 1:11:57 PM

^ yeah it was a bit much, but outside of using views I think was the easiest way to get to the information I needed. The most recent event dates weren't the problem...it was grabbing the NumSold/TestScore that corresponded to that eventdate.

if you though that made your brain hurt from what i read in my sql book, sql can go up to 32 levels deep! Who writes those things?

10/27/2006 1:21:09 PM

^32 deep? 'tis a better man than i that can keep track of that many.

10/27/2006 1:24:05 PM

^^Hey as long as it works right?

That's *basically* how views work, but they seem to be much better in runtime load. So if you ever get sluggish, it should be pretty easy to build a view based on the query you posted (which is very well thought out!)

10/27/2006 1:51:28 PM