| 7:55 pm on Sep 22, 2010 (gmt 0)|
select category,style,size,price from table group by category,style
| 8:24 pm on Sep 22, 2010 (gmt 0)|
The problem with that is that MS SQL requires all values in the SELECT list to be contained in the GROUP BY clause.
I think that you will either need to massage the data on the application side (PHP, etc.) or use a CURSOR to loop through and massage the data.
Basically, you'll need to:
SELECT DISTINCT styles
Then you'll need to loop through each row of that data and find the first row that matches that style (and Category='Designer') and return the size and price data for that row.
| 8:35 pm on Sep 22, 2010 (gmt 0)|
I was afraid of that. :-) Looks like I will need a nested SELECT (which means i will need to go back and build my gridview programmatically.
| 7:15 pm on Sep 23, 2010 (gmt 0)|
Just as a follow up for this...it had me stumped for a couple of days but finally it dawned on me to:
1) Create Style and StylePrevious veriables and set both to ""
2) Select all data in the table then loop the rest of the steps in the reader
3) Set Style = the Style column from the table
4) If Style != StylePrevious then do my work (show the data).
5) Set StylePrevious = Style
I know this is not efficient but the table has less than 400 rows and there is a WHERE clause to cut it down further.
| 9:43 pm on Sep 27, 2010 (gmt 0)|
MS SQL only requires columns to be listed in the Group By clause if they're not part of an aggregate function.
So, if you don't care which value of size or price you are returned you could use:
Select category, style, Min(size), Min(price)
From table Group By category, style
Hope this helps,