0

Please login or register to vote for this query.

(click on this box to dismiss)

Q&A for people who love being outdoors enjoying nature and wilderness, and learning about the required skills and equipment

WITH CUSTOMER As (
SELECT 1 as [CUSTOMER ID], 'A' as Name
UNION SELECT 2,    'B'
UNION SELECT 3,    'C'
UNION SELECT 4,    'D'
UNION SELECT 5,    'E' )
, SALES AS (
SELECT 1 as [Sales ID], 1 as [CUSTOMER ID], 10 as [PRODUCT ID], 100 VALUE
UNION SELECT 2,    1,    12,  400
UNION SELECT 3,    1,    17,  450
UNION SELECT 4,    2,    111, 10
UNION SELECT 5,    3,    20,  1000 )

SELECT c.*, s1.[PRODUCT ID], s1.VALUE
FROM CUSTOMER c
LEFT JOIN SALES s1
  ON c.[CUSTOMER ID] = s1.[CUSTOMER ID]
LEFT JOIN SALES s2
  ON s2.[CUSTOMER ID] = s1.[CUSTOMER ID]
  AND s2.VALUE > s1.VALUE
WHERE s2.[CUSTOMER ID] IS NULL

fork query

edited sep 15 anonymous

created feb 27 12 conradfrix