[RESOLVED] cannot DISTINCT 1 field And 2 Fields at the same time why?

hey
i am trying to Distinct and load 1 field to the listview and another one as regular but it is not working why?
what i am missing ?
this is my code

Code:

---

Select DISTINCT PriceName

---

Code:

---

Dim RsQ As New ADODB.Recordset
       
RsQ.Open "Select DISTINCT PriceName,PriceCost From PriceList order by PriceName", CN
        Do While Not RsQ.EOF
        Set itm = FrmTipulimToGeneralSale.LTreat.ListItems.Add(, , RsQ!PriceName, 1)
            itm.SubItems(1) = FormatCurrency(RsQ!PriceCost)
        RsQ.MoveNext
        Loop
 RsQ.Close

---

tnx for any help
salsa31

Do you really have items in the table with the same PriceName?
Can multiple records with the same PriceName have a different PriceCost?
If so, how would you know which PriceCost you want to display?

Quote:

---

Originally Posted by Arnoutdv

Do you really have items in the table with the same PriceName?
Can multiple records with the same PriceName have a different PriceCost?
If so, how would you know which PriceCost you want to display?

---

same price name yes

Quote:

---

how would you know which PriceCost you want to display?

---

that is why i want to DISTINCT

Let's fabricate an example of your table:

Code:

---

PriceName  PriceCost
Red        10
Red        10
Red        12
Red        14
Red        14
Blue        20
Blue        20
Blue        23
Blue        23

---

Which output do you want?

each pricename has as Diffrent PriceCost

in your E.X there are 5 reds and 4 blue
i need my list to show 1 red and 1 blue
thats why i need to use the distinct

Code:

---

PriceName  PriceCost
Red        10
Red        12
Red        14
Blue        20
Blue        23

---

Which output do you want?

Distinct PriceName will give you:
- Red
- Blue

Which PriceCost should be associated with the unique values?

Quote:

---

which output do you want?

Distinct pricename will give you:
- red
- blue

which pricecost should be associated with the unique values?

---

yes sir

If you select distinct pricename ... you will get Red & Blue ... but you need more than that, right? OK. Problem is you also have PriceCost in there... how do you know which ONE of those you want? There's nothing to make them unique.

Ideally you should have had two tables... one with PriceName and an ID ... the other with ID, PriceNameID, PriceCost, and possibly some kind of date. that will then let you get back to a specific date and identify the specific price in effect.

LEss than ideal, you have it all in one table... PriceName, PriceCost and a date...

In both cases you would group by price name and get the max date... then join that back to the table on both fields and that would give you all of the PriceCosts for each of the PriceNames.


-tg

Quote:

---

Originally Posted by salsa31

yes sir

---

I take this "yes" as:
Yes the PriceCosts shall be "associated" with the (distinct) PriceName.

And since there are different (multiple) PriceCosts you will have to somehow
"gather" or "aggregate" behind the unique PriceNames - why not use the
SQL-construct which was made for these kind of "aggregations".

The Group By Clause is quite similar to Distinct, in that you can define
"the distinct bucket" for which to gather (or aggregate) certain other FieldValues.

"Select PriceName, Sum(PriceCost) From PriceList Group By PriceName"

The above has the distinct "Bucket" (PriceName) colored in magenta, the Aggregate-Function (Sum) in blue.

There's other Aggreate-Functions as e.g.:
- Avg(PriceCost) ... in case you want the Average of the PriceCosts
- Count(PriceCost) ... in case you want only the Count of the PriceCosts in the Bucket.

Olaf

Got it Tnx amigos:)

Which solution did you choose?