Get one output table from select in while-loop without counting

Question

The table purchase contains:

row    supplier | type | marker | price
       --------------------------------

1 ....   ABC    |  A   |   0    | 3.50
2 ....   ABC    |  B   |   0    | 4.40
3 ....   ABC    |  B   |   0    | 5.50
4 ....   ABC    |  C   |   1    | 8.70
5 ....   ABC    |  C   |   0    | 9.00

6 ....   DEF    |  A   |   0    | 3.80
7 ....   DEF    |  A   |   0    | 5.10
8 ....   DEF    |  D   |   0    | 9.20

9 ....   GHI    |  E   |   0    | 2.80
10 ...   GHI    |  E   |   0    | 3.30
11 ...   GHI    |  E   |   1    | 4.10
12 ...   GHI    |  E   |   0    | 5.40
13 ...   GHI    |  F   |   0    | 9.90

A query should deliver entries for type which fulfill the following conditions:

from supplier only which has at least one dataset with marker = 1
for price which is smaller than price with marker = 1 for that specific supplier

This applies to the rows 1, 2, 3, 9 and 10.

My attempt is (following Nakeuri's general guideline):

DECLARE @supplier CHAR(3), @price FLOAT
DECLARE CurName CURSOR FAST_FORWARD READ_ONLY
FOR
    SELECT SUPPLIER, PRICE
    FROM PURCHASE
    WHERE MARKER = 1
OPEN CurName
FETCH NEXT FROM CurName INTO @supplier, @price
WHILE @@FETCH_STATUS = 0
    BEGIN
        SELECT DISTINCT TYPE
        FROM PURCHASE
        WHERE SUPPLIER = @supplier AND PRICE < @price
        FETCH NEXT FROM CurName INTO @supplier, @price
    END
CLOSE CurName
DEALLOCATE CurName

This results in individual outputs per supplier:

type    (for supplier ABC)
----
 A
 B

type    (for supplier GHI)
----
 E

I'd need one output table:

type
----
 A
 B
 E

How do I have to edit the query for a merge of the two outputs into one output? The suggestion for Henrik can not be applied as my case is not a counting operation.

System:

Product: Microsoft SQL Server Standard (64-bit)
Operating System: Windows Server 2022 Standard, Version 21H2

Why are you looping at all here, why not write a proper set-based query? — Stu
– Stu, Commented Oct 18, 2023 at 13:13
I imagine the instructor is telling you to use a while-loop?...but really, no one would. — Nick Abbot
– Nick Abbot, Commented Oct 18, 2023 at 13:30

Stu · Accepted Answer · 2023-10-18 13:40:28Z

0

The set-based query you need (rather than a RBAR approach) is as follows:

select *
from t
cross apply(
  select Min(price) MinPrice
  from t t2
  where t2.Supplier = t.Supplier and t2.marker = 1
)m
where t.Price < MinPrice;

This shows you the rows identified, and from this you can return your required results with

select distinct type from...

See a working demo

Result:

edited Oct 18, 2023 at 13:40

answered Oct 18, 2023 at 13:21

Stu

32.7k6 gold badges17 silver badges34 bronze badges

Sign up to request clarification or add additional context in comments.

6 Comments

Nick Abbot Over a year ago

why not just approve the edit? distinct

Stu Over a year ago

I think it would be useful to the OP to also realise the intermediate step of identifying the rows speciified that meet the criteria

Nick Abbot Over a year ago

Ah, ok, fair enough. OP already knows about that. They have a "SELECT DISTINCT TYPE" in their example and they clearly show the output required.

siggemannen Over a year ago

If i understand the OP correctly, there might be multiple rows with marker = 1 per supplier. The old solution might generate multiple rows per supplier / row. I might be overthinking this too :D

Nick Abbot Over a year ago

@siggemannen I think Stu has that covered with min(price). Gets the lowest price of all marker=1

|

Charlieface · Accepted Answer · 2023-10-18 14:09:42Z

0

You can use a window function here

SELECT DISTINCT
  t.type
FROM (
    SELECT *,
      MIN(CASE WHEN t.marker = 1 THEN t.price END) OVER
         (PARTITION BY t.supplier) AS MinPrice
    FROM t
) t
WHERE t.Price < t.MinPrice;

db<>fiddle

answered Oct 18, 2023 at 14:09

Charlieface

78.6k8 gold badges35 silver badges77 bronze badges

Collectives™ on Stack Overflow

Get one output table from select in while-loop without counting

2 Answers 2

6 Comments

Comments

Your Answer

Linked

Hot Network Questions

Collectives™ on Stack Overflow

2 Answers 2

6 Comments

Comments

Your Answer

Sign up or log in

Post as a guest

Linked

Related