Select Distinct

1. Junior Member
Join Date
Dec 1969
Posts
27

## Select Distinct

SELECT DISTINCT Code<BR>FROM Price<BR>WHERE StyleNumber = &#039;MMColParam&#039;<BR>ORDER BY Quantity<BR><BR>How can I select Distinct the code field only and Order by Quantity

2. Senior Member
Join Date
Dec 1969
Posts
96,118

## Query doesn't make sense...

SELECT DISTINCT Code<BR>FROM Price<BR>WHERE StyleNumber = &#039;MMColParam&#039;<BR>ORDER BY Quantity<BR><BR>Consider: You have a table like this:<BR><BR>Code Quantity<BR>17 -- 3<BR>17 -- 5<BR>17 -- 12<BR>22 -- 10<BR>22 -- 11<BR><BR>What does it *MEAN* to say "SELECT DISTINCT Code ORDER BY Quantity"???<BR><BR>Order by the *sum* of the quantities for each code? Or order by the maximum of the quantities for each code? Or the minimum of the quantities for each code? Or the average of the quantities...<BR><BR>Well, you get the idea! <BR><BR>If you can tell me *which* quantity you want to order by, the rest is easy!<BR><BR>And, in fact, you might not even *need* the DISTINCT!<BR><BR>SELECT Code, Max(Quantity) <BR>FROM Price<BR>WHERE StyleNumber = &#039;MMColParam&#039;<BR>GROUP BY Code<BR>ORDER BY Max(Quantity)<BR><BR>That works for Max(Quantity) or Min(...) or Sum(...) or Avg(...) <BR><BR>Better than DISTINCT.<BR><BR>SO... Explain what you want in English, and we can help you translate it to SQL. <BR><BR>

3. Junior Member
Join Date
Dec 1969
Posts
27

## Query explanation

I have a table (Price)with the following fields:<BR><BR>Quantity,Code<BR>10 , 2A<BR>100 , 2A<BR>1000 , B<BR>10000 , 2C<BR>100000 , 2C<BR><BR>I need to see the results like the following (in horizontal loop)<BR><BR>2AB2C <BR>

4. Senior Member
Join Date
Dec 1969
Posts
96,118

## You did NOT answer the question...

Is it the *TOTAL QUANTITY* of 2A that puts it first? Or is it the maximum quantity of a given instance.<BR><BR>Suppose *these* are the quantities and codes, instead of what you showed:<BR><BR>600 2A<BR>700 2A<BR>1000 B<BR>200 2C<BR>900 2C<BR><BR>What *then* would be the order???? <BR><BR>Are we supposed to *VIEW* that as:<BR><BR>600+700 = 1300 -- 2A<BR>1000 -- B<BR>200+900 = 1100 -- 2C<BR><BR>Or are we supposed to VIEW that as<BR><BR>700 2A<BR>1000 B<BR>900 2C<BR><BR>Using only the maximal quantities? Or are we supposed to VIEW that as<BR><BR>600 2A<BR>1000 B<BR>200 2C<BR><BR>Using only the minimal quantities????<BR><BR>You answered *nothing* with your example!<BR><BR>

#### Posting Permissions

• You may not post new threads
• You may not post replies
• You may not post attachments
• You may not edit your posts
•