Database

strLseQry = "SELECT L_NAME, F_NAME1, F_NAME2,  ADD_INFO FROM LEASES WHERE LEASE_ID = '"&aLeaseList&"'"

strLseQry = "SELECT L_NAME, F_NAME1, F_NAME2, ADD_INFO FROM LEASES WHERE LEASE_ID IN ("&aLeaseList&")"

WITH ORDERED_RECORDS AS(
		SELECT	
			 row_number() OVER (ORDER BY Records.[Plate Number] ASC) AS rowID
			,Records.[Officer ID]
			,DriverInfo.[Plate Number]
			,DriverInfo.[License Number]
			,DriverInfo.[Conduction Number]
			,DriverInfo.[Vehicle Category]
			,DriverInfo.[Vehicle Type]
			,DriverInfo.[Vehicle Brand]
			,DriverInfo.[Last Name]
			,DriverInfo.[First Name]
			,DriverInfo.[Middle Name]
			,DriverInfo.[Birth Date]
			,DriverInfo.[Registered Address]
			,DriverInfo.[Address' City Code]
			,Records.[Violation Commited]
			,Violations.[Violation Code]
			,Records.[Street Name]
			,Records.[City of Apprehension]
			,Cities.[City Code]
			,Violations.[Fine Amount]
			,Records.[Date/Time Apprehended]
		FROM Violations
			,DriverInfo
			,Records
			,Cities
		WHERE		Records.[Plate Number]			like DriverInfo.[Plate Number]
				and Records.[Violation Commited]	like Violations.Violations
				and Records.[City of Apprehension]	like Cities.City
)
DELETE FROM Records
WHERE Records.[Plate Number] 
	IN (
		SELECT	[Plate Number] 
		FROM	ORDERED_RECORDS 
		WHERE	rowID=1
	)

fracalifa wrote:

What's to do when I nead all the items from sells table row ?

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)

A Wong wrote:

You mean 4 columns from the sells table?

;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

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 ;