Welcome to WebmasterWorld Guest from

Forum Moderators: open

Message Too Old, No Replies

How do I form this SQL statement?



5:35 pm on Sep 22, 2010 (gmt 0)

5+ Year Member

I have the following data in an MS SQL database:

Category Style Size Price
Designer F123 2 3.99
Designer F123 3 4.99
Designer F123 4 5.99
Designer F456 2 3.99
Economy1 F789 3 5.99

I want to select only from Designer, I want to select the entire row, but with DISTINCT styles...for example the output of the above would be:

Category Style Size Price
Designer F123 2 3.99
Designer F456 2 3.99

I've tried several options but to no avail. Any thoughts?


7:55 pm on Sep 22, 2010 (gmt 0)

WebmasterWorld Senior Member 10+ Year Member

Try this:

select category,style,size,price from table group by category,style


8:24 pm on Sep 22, 2010 (gmt 0)

WebmasterWorld Administrator lifeinasia is a WebmasterWorld Top Contributor of All Time 5+ Year Member Top Contributors Of The Month

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:
FROM YourTable
WHERE Category='Designer'

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)

5+ Year Member

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)

5+ Year Member

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)

5+ Year Member

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,

Featured Threads

Hot Threads This Week

Hot Threads This Month