Welcome to WebmasterWorld Guest from 54.224.103.186

Forum Moderators: open

Message Too Old, No Replies

How do I form this SQL statement?

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

Junior Member

5+ Year Member

joined:Dec 4, 2009
posts: 186
votes: 0


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
etc...

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 Sept 22, 2010 (gmt 0)

Senior Member

WebmasterWorld Senior Member 10+ Year Member

joined:June 17, 2002
posts:1182
votes: 5


Try this:

select category,style,size,price from table group by category,style
8:24 pm on Sept 22, 2010 (gmt 0)

Moderator from US 

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

joined:Dec 10, 2005
posts:5599
votes: 29


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
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 Sept 22, 2010 (gmt 0)

Junior Member

5+ Year Member

joined:Dec 4, 2009
posts: 186
votes: 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.

Thanks!
7:15 pm on Sept 23, 2010 (gmt 0)

Junior Member

5+ Year Member

joined:Dec 4, 2009
posts: 186
votes: 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 Sept 27, 2010 (gmt 0)

New User

10+ Year Member

joined:May 17, 2006
posts: 24
votes: 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,
-Peter