Click here to Skip to main content
15,499,046 members
Home / Discussions / Database
   

Database

 
GeneralRe: How to get the first rows of a relational table (depending of the last date of change) Pin
A Wong16-Jul-08 4:15
MemberA Wong16-Jul-08 4:15 
GeneralRe: How to get the first rows of a relational table (depending of the last date of change) Pin
fracalifa16-Jul-08 4:32
Memberfracalifa16-Jul-08 4:32 
GeneralRe: How to get the first rows of a relational table (depending of the last date of change) Pin
A Wong16-Jul-08 4:41
MemberA Wong16-Jul-08 4:41 
GeneralRe: How to get the first rows of a relational table (depending of the last date of change) Pin
fracalifa16-Jul-08 5:10
Memberfracalifa16-Jul-08 5:10 
GeneralRe: How to get the first rows of a relational table (depending of the last date of change) Pin
A Wong16-Jul-08 5:38
MemberA Wong16-Jul-08 5:38 
GeneralRe: How to get the first rows of a relational table (depending of the last date of change) Pin
A Wong16-Jul-08 5:50
MemberA Wong16-Jul-08 5:50 
AnswerRe: How to get the first rows of a relational table (depending of the last date of change) Pin
leoinfo16-Jul-08 5:29
Memberleoinfo16-Jul-08 5:29 
AnswerRe: How to get the first rows of a relational table (depending of the last date of change) Pin
leoinfo16-Jul-08 5:39
Memberleoinfo16-Jul-08 5:39 
... 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 ; 

GeneralRe: How to get the first rows of a relational table (depending of the last date of change) Pin
fracalifa16-Jul-08 5:56
Memberfracalifa16-Jul-08 5:56 
QuestionHow to Make Application to Sync on Remote Site Pin
Nassoro Ally16-Jul-08 1:39
MemberNassoro Ally16-Jul-08 1:39 
QuestionReports are not visible on client machine Pin
meetaqadir15-Jul-08 21:54
Membermeetaqadir15-Jul-08 21:54 
QuestionOLEDB with Excel problem Pin
acroitoriu15-Jul-08 21:52
Memberacroitoriu15-Jul-08 21:52 
AnswerRe: OLEDB with Excel problem Pin
acroitoriu15-Jul-08 22:23
Memberacroitoriu15-Jul-08 22:23 
Questionfacing probs in nested cursor Pin
Member 387988115-Jul-08 20:56
MemberMember 387988115-Jul-08 20:56 
AnswerRe: facing probs in nested cursor Pin
Alsvha16-Jul-08 0:28
MemberAlsvha16-Jul-08 0:28 
Questionbytes retrieval Pin
vicki52815-Jul-08 19:19
Membervicki52815-Jul-08 19:19 
AnswerRe: bytes retrieval Pin
Alsvha16-Jul-08 0:28
MemberAlsvha16-Jul-08 0:28 
QuestionHow do i convert a varchar field into the datetime data type Pin
Rameez Raja15-Jul-08 19:12
MemberRameez Raja15-Jul-08 19:12 
AnswerRe: How do i convert a varchar field into the datetime data type Pin
Alsvha16-Jul-08 0:31
MemberAlsvha16-Jul-08 0:31 
QuestionDATABASE CONNECTIVITY Pin
murphy7715-Jul-08 16:58
Membermurphy7715-Jul-08 16:58 
AnswerRe: DATABASE CONNECTIVITY Pin
Ashfield15-Jul-08 22:45
MemberAshfield15-Jul-08 22:45 
QuestionConnecting to an ODBC DataSource Pin
ffowler15-Jul-08 15:17
Memberffowler15-Jul-08 15:17 
QuestionDatabase Publishing Wizard alternatives Pin
Todd Smith15-Jul-08 10:15
MemberTodd Smith15-Jul-08 10:15 
AnswerRe: Database Publishing Wizard alternatives Pin
Alsvha16-Jul-08 0:34
MemberAlsvha16-Jul-08 0:34 
Questionautomatica fire event Pin
Member 387988115-Jul-08 5:12
MemberMember 387988115-Jul-08 5:12 

General General    News News    Suggestion Suggestion    Question Question    Bug Bug    Answer Answer    Joke Joke    Praise Praise    Rant Rant    Admin Admin   

Use Ctrl+Left/Right to switch messages, Ctrl+Up/Down to switch threads, Ctrl+Shift+Left/Right to switch pages.