How to get the first rows of a relational table (depending of the last date of change)
-
-
Thank you for your answer, you are right S.id is useless. I played with your solution but on this way I only can get one item from the sells table ( = sellsdata). What's to do when I nead all the items from sells table row ? tnx Frank
-
fracalifa wrote:
What's to do when I nead all the items from sells table row ?
Then you can go back to using a join to do so.
-
This was my first suggestion, but then I'm already at the initial position of the problem ! more ideas ?
I'm confused. Originally from what I understand, your question was, how do you get the data such that you get all the rows of the first table, combined with 1 row of sell table for each row of the first table. Now you say you want all of the rows from sell? What exactly do you want? Give me examples please.
-
I'm confused. Originally from what I understand, your question was, how do you get the data such that you get all the rows of the first table, combined with 1 row of sell table for each row of the first table. Now you say you want all of the rows from sell? What exactly do you want? Give me examples please.
Your first understanding is right. I need for all customers their last sold product. But the last product information consits of 4 items. So the solution grid should look like that ID-----Name----Address------> LastSoldItem----ProductNr.----ProductPrice----LastSellingDate 1111--Miller--1.st Avn----------Ship-----------00112233--------1000,00-----------05/09/2008 1123--Riven---Hallstreet--------Car------------00055564--------2000,00-----------07/08/2008 etc. Every customer has sold many products and I want to see only the last sold product consisting of 4 items. The tables are related by the customer-ID. With your suggested solution I only get one item of the last sold product. (and not all the 4 I need) I hope this clears your confusion.... tnx frank
-
Hi all, I want to fill a grid (no hirarchical) where to combine customer data and the last selling item of each customer in a row (getting only the last sellings depending on sells_date). My idea was
SELECT C.id,C.customerdata,S.id,S.sellsdata FROM dbo.customer AS C LEFT OUTER JOIN (SELECT TOP 1 id,sellsdata FROM dbo.sells ORDER BY sells_date DESC) AS S ON C.id=S.id
Whith this string I get only one selling item and not one item for each. How does the correct SQL-String look likes ? tnx in advance Frank
You should ALWAYS provide the actual fields for each table to avoid misunderstandings. This should solve the problem...
;WITH LatestSells AS (
SELECT s1.*
FROM dbo.Sells AS s1
INNER JOIN (
SELECT DISTINCT Customer_ID
, ( SELECT TOP 1 Sell_ID
FROM dbo.Sells
WHERE Customer_ID = sx.Customer_ID
ORDER BY Sell_Date DESC
) AS Sell_ID
FROM dbo.Sells AS sx
) AS s2 ON s1.Customer_ID = s2.Customer_ID AND s1.Sell_ID = s2.Sell_ID
)
SELECT c.*, s.*
FROM dbo.Customers AS c
LEFT OUTER JOIN LatestSells AS s
ON c.Customer_ID = s.Customer_ID -
Your first understanding is right. I need for all customers their last sold product. But the last product information consits of 4 items. So the solution grid should look like that ID-----Name----Address------> LastSoldItem----ProductNr.----ProductPrice----LastSellingDate 1111--Miller--1.st Avn----------Ship-----------00112233--------1000,00-----------05/09/2008 1123--Riven---Hallstreet--------Car------------00055564--------2000,00-----------07/08/2008 etc. Every customer has sold many products and I want to see only the last sold product consisting of 4 items. The tables are related by the customer-ID. With your suggested solution I only get one item of the last sold product. (and not all the 4 I need) I hope this clears your confusion.... tnx frank
fracalifa wrote:
Every customer has sold many products and I want to see only the last sold product consisting of 4 items.
fracalifa wrote:
With your suggested solution I only get one item of the last sold product. (and not all the 4 I need)
You mean 4 columns from the sells table?
-
You should ALWAYS provide the actual fields for each table to avoid misunderstandings. This should solve the problem...
;WITH LatestSells AS (
SELECT s1.*
FROM dbo.Sells AS s1
INNER JOIN (
SELECT DISTINCT Customer_ID
, ( SELECT TOP 1 Sell_ID
FROM dbo.Sells
WHERE Customer_ID = sx.Customer_ID
ORDER BY Sell_Date DESC
) AS Sell_ID
FROM dbo.Sells AS sx
) AS s2 ON s1.Customer_ID = s2.Customer_ID AND s1.Sell_ID = s2.Sell_ID
)
SELECT c.*, s.*
FROM dbo.Customers AS c
LEFT OUTER JOIN LatestSells AS s
ON c.Customer_ID = s.Customer_ID... and - as an addition - here is some code that everyone can test..
CREATE TABLE #Customers (
Customer_ID INT,
Customer_Name NVARCHAR(50)
) ;
INSERT INTO #Customers (Customer_ID, Customer_Name)
SELECT 1, 'John Smith' ;
INSERT INTO #Customers (Customer_ID, Customer_Name)
SELECT 2, 'Kate Taylor' ;
CREATE TABLE #Purchases (
Customer_ID INT,
Purchase_ID INT,
Product_Name NVARCHAR(50),
Quantity DECIMAL(8,2),
Sell_Date DATETIME
) ;
INSERT INTO #Purchases (Customer_ID , Purchase_ID , Product_Name , Quantity, Sell_Date)
SELECT 1, 1, 'Seagate HDD' , 4 , '2008-07-16 08:45' ;
INSERT INTO #Purchases (Customer_ID , Purchase_ID , Product_Name , Quantity, Sell_Date)
SELECT 2, 2, 'Maxtor HDD' , 2 , '2008-07-16 08:47' ;
INSERT INTO #Purchases (Customer_ID , Purchase_ID , Product_Name , Quantity, Sell_Date)
SELECT 1, 3, 'Mouse' , 2 , '2008-07-16 08:52' ;
INSERT INTO #Purchases (Customer_ID , Purchase_ID , Product_Name , Quantity, Sell_Date)
SELECT 2, 4, 'LCD' , 1 , '2008-07-16 09:15' ;
INSERT INTO #Purchases (Customer_ID , Purchase_ID , Product_Name , Quantity, Sell_Date)
SELECT 1, 5, 'Keyboard' , 7 , '2008-07-16 09:25' ;
INSERT INTO #Purchases (Customer_ID , Purchase_ID , Product_Name , Quantity, Sell_Date)
SELECT 1, 6, 'KVM' , 2 , '2008-07-16 10:30' ;;WITH LatestSells AS (
SELECT s1.*
FROM #Purchases AS s1
INNER JOIN (
SELECT DISTINCT Customer_ID
, ( SELECT TOP 1 Purchase_ID
FROM #Purchases
WHERE Customer_ID = sx.Customer_ID
ORDER BY Sell_Date DESC
) AS Purchase_ID
FROM #Purchases AS sx
) AS s2
ON s1.Customer_ID = s2.Customer_ID AND s1.Purchase_ID = s2.Purchase_ID
)
SELECT c.*, s.*
FROM #Customers AS c
LEFT OUTER JOIN LatestSells AS s
ON c.Customer_ID = s.Customer_ID ;DROP TABLE #Customers ;
DROP TABLE #Purchases ; -
fracalifa wrote:
Every customer has sold many products and I want to see only the last sold product consisting of 4 items.
fracalifa wrote:
With your suggested solution I only get one item of the last sold product. (and not all the 4 I need)
You mean 4 columns from the sells table?
A Wong wrote:
You mean 4 columns from the sells table?
If that's the case then: SELECT C.id,C.customerdata,S.id,S.LastSoldItem, S.ProductNr, S.ProductPrice, S.LastSellingDate FROM dbo.customer AS C LEFT OUTER JOIN (SELECT s1.id,s1.LastSoldItem,s1.ProductNr, s1.ProductPrice, s1 .LastSellingDate FROM dbo.sells s1 where s1.LastSellingDate = (select max(s2.LastSellingDate) from dbo.sells s2 where s1.id = s2.id)) AS S ON C.id=S.id
-
You should ALWAYS provide the actual fields for each table to avoid misunderstandings. This should solve the problem...
;WITH LatestSells AS (
SELECT s1.*
FROM dbo.Sells AS s1
INNER JOIN (
SELECT DISTINCT Customer_ID
, ( SELECT TOP 1 Sell_ID
FROM dbo.Sells
WHERE Customer_ID = sx.Customer_ID
ORDER BY Sell_Date DESC
) AS Sell_ID
FROM dbo.Sells AS sx
) AS s2 ON s1.Customer_ID = s2.Customer_ID AND s1.Sell_ID = s2.Sell_ID
)
SELECT c.*, s.*
FROM dbo.Customers AS c
LEFT OUTER JOIN LatestSells AS s
ON c.Customer_ID = s.Customer_ID