CODEWITHSUNDEEP
sqlsql-server
Jeffrey Kramer
Jeffrey Kramer

Reputation: 1345

SQL to retrieve only the minimum value in query with multiple joins

Say I have a query with several joins across three tables:

SELECT
    main_data.id,
    main_data.dt,
    main_data.seq_num,
    main_data.sale_amt,
    main_data.sale_cd,
    promo.promo_cd,
    payment.card,
    payment.priority
FROM
    main_data
INNER JOIN promo
    ON promo.id = main_data.id
    AND main_data.dt >= promo.start_dt
    AND main_data.dt <= promo_end_dt
INNER JOIN payment
    ON payment.sale_cd = main_data.sale_cd
    AND payment.card = main_data.card
WHERE 
    main_data.dt BETWEEN '2013-10-12' AND '2013-10-12'

Basically, sales are tied to a form of payment (payment) and a promotion (promo). There are a few problems with mapping promo codes to eligible payments (one-to-many relationships).

At this point, there are possible duplicate records from main-data. Therefore, I need to use the payment.priority that has the lowest value. How can I extract only the line with the lowest value for that field? I tried nesting this as a sub-query but couldn't make it work properly. The database itself is totally static and I'm unable to change the schema in any way.

Upvotes: 0

Views: 93

Answers (3)

Santhosh
Santhosh

Reputation: 1791

You have mentioned as main attributes give duplicates, so I have assumed these to be GROUP BY columns

WITH A AS
(
SELECT
    main_data.id,
    main_data.dt,
    main_data.seq_num,
    main_data.sale_amt,
    main_data.sale_cd,
    promo.promo_cd,
    payment.card,
    payment.priority
    , ROW_NUMBER() OVER(PARTITION BY main_data.id, main_data.dt, main_data.seq_num,   main_data.sale_amt, main_data.sale_cd ORDER BY payment.priority) AS RN
FROM
    main_data
INNER JOIN promo
    ON promo.id = main_data.id
    AND main_data.dt >= promo.start_dt
    AND main_data.dt <= promo_end_dt
INNER JOIN payment
    ON payment.sale_cd = main_data.sale_cd
    AND payment.card = main_data.card
WHERE 
    main_data.dt BETWEEN '2013-10-12' AND '2013-10-12'
)

SELECT * FROM A 
WHERE RN = 1

Upvotes: 1

rwking
rwking

Reputation: 1032

You could try this. The row_number function groups the items in the PAYMENT table by sale_cd, then orders the entries by priority asc. Thus, row_num = '1' should give you the lowest value for priority grouped by sale_cd.

WITH CTE AS (CARD, PRIORITY, SALE_CD, ROW_NUM)
AS
(
  SELECT CARD
         , PRIORITY
         , SALE_CD
         , ROW_NUMBER() OVER(PARTITION BY SALE_CD ORDER BY PRIORITY ASC) AS ROW_NUM
  FROM PAYMENT
)

SELECT
    main_data.id,
    main_data.dt,
    main_data.seq_num,
    main_data.sale_amt,
    main_data.sale_cd,
    promo.promo_cd,
    CTE.card,
    CTE.priority
FROM
    main_data
INNER JOIN promo
    ON promo.id = main_data.id
    AND main_data.dt >= promo.start_dt
    AND main_data.dt <= promo_end_dt
INNER JOIN CTE CTE
    ON CTE.sale_cd = main_data.sale_cd
    AND CTE.card = main_data.card
WHERE 
    main_data.dt BETWEEN '2013-10-12' AND '2013-10-12'
    AND CTE.ROW_NUM = '1'

Upvotes: 1

MichaelEvanchik
MichaelEvanchik

Reputation: 1766

SELECT
main_data.id,
main_data.dt,
main_data.seq_num,
main_data.sale_amt,
main_data.sale_cd,
promo.promo_cd,
payment.card,
min(payment.priority)
FROM
main_data
INNER JOIN promo
ON promo.id = main_data.id
AND main_data.dt >= promo.start_dt
AND main_data.dt <= promo_end_dt
INNER JOIN payment
ON payment.sale_cd = main_data.sale_cd
AND payment.card = main_data.card
WHERE 
main_data.dt BETWEEN '2013-10-12' AND '2013-10-12'
group by main_data.id,
main_data.dt,
main_data.seq_num,
main_data.sale_amt,
main_data.sale_cd,
promo.promo_cd,
payment.card

Upvotes: 0

Related Questions