homepage Welcome to WebmasterWorld Guest from 54.226.18.74
register, free tools, login, search, pro membership, help, library, announcements, recent posts, open posts,
Become a Pro Member
Home / Forums Index / Code, Content, and Presentation / Databases
Forum Library, Charter, Moderators: physics

Databases Forum

    
How do I form this SQL statement?
IntegrityWebDev




msg:4205388
 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




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

Try this:

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

LifeinAsia




msg:4205460
 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




msg:4205463
 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




msg:4205991
 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




msg:4207719
 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

Global Options:
 top home search open messages active posts  
 

Home / Forums Index / Code, Content, and Presentation / Databases
rss feed

All trademarks and copyrights held by respective owners. Member comments are owned by the poster.
Home ¦ Free Tools ¦ Terms of Service ¦ Privacy Policy ¦ Report Problem ¦ About ¦ Library ¦ Newsletter
WebmasterWorld is a Developer Shed Community owned by Jim Boykin.
© Webmaster World 1996-2014 all rights reserved