How do I form this SQL statement?

Forum Moderators: open

Message Too Old, No Replies

How do I form this SQL statement?

IntegrityWebDev

5:35 pm on Sep 22, 2010 (gmt 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?

Frank_Rizzo

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

Try this:

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

LifeinAsia

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

IntegrityWebDev

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.

Thanks!

IntegrityWebDev

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.

DWarp9

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,
-Peter