Arranging table output

Question

Arranging table output

SkyBVI

SSCoach

Points: 15128
More actions
October 14, 2010 at 11:37 am

#388008

Hi,
I want to arrange an output from a table by writing a sql code..
The colums are product id, name , category,description, image path , price.
I want the output to be grouped by category and under each category should be the price aranged in desc order...
i tried :-
SELECT Product_ID, Name, Description,Category, Image_Path, price from Webitems_test1
GROUP by Category,Product_ID,Name,Description,Image_path,price
order by price desc;
But here iam getting all rows arranged wrt to price desc ( not grouped by category)...
WHAT should be the query??
Regards,
Sushant
Regards
Sushant Kumar
MCTS,MCP

Viewing 15 posts - 1 through 15 (of 18 total)

You must be logged in to reply to this topic. Login to reply

Gail Shaw SSC Guru Points: 1004504 More actions · Answer 1

Group by is used for aggregation. (min, max, sum, avg, etc)

From the sound of things, you just want the rows ordered first by category then, within that, bty price. So tell SQL exactly that:

SELECT Product_ID, Name, Description,Category, Image_Path, price

FROM Webitems_test1

ORDER BY Category ASC, price desc;

May I suggest you hit Books Online and do a little reading on Group By and Order By, see exactly what they do and why they're used?

Gail Shaw
Microsoft Certified Master: SQL Server, MVP, M.Sc (Comp Sci)
SQL In The Wild: Discussions on DB performance with occasional diversions into recoverability

We walk in the dark places no others will enter
We stand on the bridge and no one may pass

Lowell SSC Guru Points: 323518 More actions · Answer 2

your query is good, all you need to do is change the ORDER BY in order to get it in the desired visual representation.

order by category,price desc;

Lowell

--help us help you! If you post a question, make sure you include a CREATE TABLE... statement and INSERT INTO... statement into that table to give the volunteers here representative data. with your description of the problem, we can provide a tested, verifiable solution to your question! asking the question the right way gets you a tested answer the fastest way possible!

SkyBVI SSCoach Points: 15128 More actions · Answer 3

@ gail & lowell

Ya,

That was correct, I guess I didnt needed GROUP BY ....

Order by category,price was enough for me...

Thanks.

Regards,

Sushant

Regards
Sushant Kumar
MCTS,MCP

SkyBVI SSCoach Points: 15128 More actions · Answer 4

And also,

How to make a new table from the output of this query?

Regards,

Sushant

Regards
Sushant Kumar
MCTS,MCP

Gail Shaw SSC Guru Points: 1004504 More actions · Answer 5

Look up SELECT INTO in the aforementioned Books Online.

Though, if you're doing that, ditch the Order By. An order by on an insert or select into in no way guarantees the order of rows in the destination table (since rows have no order), and does not in any way guarantee the order that data will be returned if querying said table.

Why do you want to insert the unfiltered contents of one table into another? Doesn't make any logical sense.

Gail Shaw
Microsoft Certified Master: SQL Server, MVP, M.Sc (Comp Sci)
SQL In The Wild: Discussions on DB performance with occasional diversions into recoverability

We walk in the dark places no others will enter
We stand on the bridge and no one may pass

SkyBVI SSCoach Points: 15128 More actions · Answer 6

@ gail

Just to look good another table without an order by clause..

But ya, the order was random when it copied the data from 1 table to another.

Regards,

Sushant

Regards
Sushant Kumar
MCTS,MCP

Gail Shaw SSC Guru Points: 1004504 More actions · Answer 7

SKYBVI (10/14/2010)
@ gail
Just to look good another table without an order by clause..

Not sure what you meant there...

Gail Shaw
Microsoft Certified Master: SQL Server, MVP, M.Sc (Comp Sci)
SQL In The Wild: Discussions on DB performance with occasional diversions into recoverability

We walk in the dark places no others will enter
We stand on the bridge and no one may pass

SkyBVI SSCoach Points: 15128 More actions · Answer 8

@ gail

You asked me why you want to do it , any logical sense

So, i replied that the new table should look nice, similiar to the output from the previous table with order by clause.

Regards,

Sushant

Regards
Sushant Kumar
MCTS,MCP

Gail Shaw SSC Guru Points: 1004504 More actions · Answer 9

But that won't work, because only an order by on the select statement guarantees order, not 'order' in a table, not order by on an insert.

Gail Shaw
Microsoft Certified Master: SQL Server, MVP, M.Sc (Comp Sci)
SQL In The Wild: Discussions on DB performance with occasional diversions into recoverability

We walk in the dark places no others will enter
We stand on the bridge and no one may pass

SkyBVI SSCoach Points: 15128 More actions · Answer 10

@ gail

Ya, i noticed that there was no use, as the rows were not according to that..(order by) in the new table..

I had to delete 1-2 columns( as per req.) from the earlier table, so I had to make a new table also.

Regards,

Sushant

Regards
Sushant Kumar
MCTS,MCP

Jeff Moden SSC Guru Points: 1004363 More actions · Answer 11

SKYBVI (10/14/2010)
@ gail
Ya, i noticed that there was no use, as the rows were not according to that..(order by) in the new table..
I had to delete 1-2 columns( as per req.) from the earlier table, so I had to make a new table also.
Regards,
Sushant

They can be if you use ROW_NUMBER over and include the column in the new table.

Also... you don't need to precreate the table just because you had to delete some columns... just don't include them as part of the SELECT/INTO. Heh... please tell me you didn't use SELECT * in the first attempt. 😉

--Jeff Moden

RBAR is pronounced "ree-bar" and is a "Modenism" for Row-By-Agonizing-Row.
First step towards the paradigm shift of writing Set Based code:
________Stop thinking about what you want to do to a ROW... think, instead, of what you want to do to a COLUMN.

Change is inevitable... Change for the better is not.

Helpful Links:
How to post code problems
How to Post Performance Problems
Create a Tally Function (fnTally)

SkyBVI SSCoach Points: 15128 More actions · Answer 12

@ jeff

in first table i had approx 7 columns, and the develoepr wanted only few imp colums in a table (around 4-5), so I had to delete the 2-3 columns and then make a new table for him.

I could have done just deleting also, but i thought the output(lookwise) of the table should look better with an order by so, i wanted to implement in the new table which was not successful.

I will now try row_number.

Thanks,

Sushant

Regards
Sushant Kumar
MCTS,MCP

SkyBVI SSCoach Points: 15128 More actions · Answer 13

@ jeff

Using select * in first attempt.....good catch...

but acutally there were many more columns also ,which i didnt disclose here, so that way had to mention just the required columns.

Regards,

Sushant

Regards
Sushant Kumar
MCTS,MCP

Gail Shaw SSC Guru Points: 1004504 More actions · Answer 14

SKYBVI (10/15/2010)
I will now try row_number.

Don't. It still won't enforce order in the table or order when selected. It'll just give you a convenient column to Order By.

Gail Shaw
Microsoft Certified Master: SQL Server, MVP, M.Sc (Comp Sci)
SQL In The Wild: Discussions on DB performance with occasional diversions into recoverability

We walk in the dark places no others will enter
We stand on the bridge and no one may pass