Please start any new threads on our new
site at https://forums.sqlteam.com. We've got lots of great SQL Server
experts to answer whatever question you can come up with.
| Author |
Topic |
|
taylo
Yak Posting Veteran
82 Posts |
 Posted - 2002-02-13 : 15:39:20
|
| Select Distinct Size from PriceProduct where ProductID = @prodOrder by ListOrderWhen I try to do this it tells me I can't because I need to have "ListOrder" in the Select statement. But I don;t want it in the select statement because If I put it there nothing is distinct.How can I write this query to pull out all these sizes but order them by the list order? I tried Group By but no luck.Thanks,Rob |
|
|
robvolk
Most Valuable Yak
15732 Posts |
Posted - 2002-02-13 : 15:48:04
|
| Using DISTINCT removes duplicate values. In doing so, it effectively removes all columns that are not specified in the SELECT list; that's why it can't ORDER BY ListOrder. The only way to ORDER BY ListOrder is to include it in the SELECT or lose the DISTINCT clause.Edited by - robvolk on 02/13/2002 15:48:29 |
 |
|
|
AjarnMark
SQL Slashing Gunting Master
3246 Posts |
Posted - 2002-02-13 : 16:53:17
|
| From a little more theoretical point of view, the problem is that if adding ListOrder to the SELECT changes the Distinct results, then the problem for SQL Server would be, without ListOrder in the SELECT, then WHICH ListOrder for this Distinct value would it sort on. Basically, there is no more correlation between a single distinct value and the data in its original row.------------------------GENERAL-ly speaking... |
|
|
|
izaltsman
A custom title
1139 Posts |
Posted - 2002-02-13 : 18:06:30
|
| And to establish the correlation which Mark mentions in his post, you can use MIN or MAX function in conjunction with GROUP BY clause. So if you wanted to sort on the smallest ListOrder available for each distinct size, you would do something along the lines of: SELECT Size FROM PriceProduct WHERE ProductID = @prod GROUP BY Size ORDER BY min(ListOrder) |
|
|
|
|
|
|
Dictinct Ordering