Sorting Results - SQL Server Forums

Please start any new threads on our new site at https://forums.sqlteam.com. We've got lots of great SQL Server experts to answer whatever question you can come up with.

All Forums

General SQL Server Forums

New to SQL Server Programming

Sorting Results

Author

Topic

Shodman
Starting Member

2 Posts

Posted - 2007-03-28 : 08:24:13

Hi

I am VERY new to SQL Server 2005, so please be gentle with me.

I have created a very basic report that COUNTS a list of transactions by Supplier. Now I need to SORT the results.

Can you help?

Cheers
Shodman

harsh_athalye
Master Smack Fu Yak Hacker

5581 Posts

Posted - 2007-03-28 : 08:27:12

sort by which column?

You need to make use of ORDER BY clause along with column name to sort the result.

Harsh Athalye
India.
"The IMPOSSIBLE is often UNTRIED"

nr
SQLTeam MVY

12543 Posts

Posted - 2007-03-28 : 08:27:57

add an order by clause

select supplier, num = count(*)
from tbl
group by supplier
order by supplier

select supplier, num = count(*)
from tbl
group by supplier
order by count(*)

==========================================
Cursors are useful if you don't know sql.
DTS can be used in a similar way.
Beer is not cold and it isn't fizzy.

Shodman
Starting Member

2 Posts

Posted - 2007-03-28 : 08:37:48

Thanks for quick response guys, however I did advise i'm VERY new to SQL. I'm not into using syntax.

Hopefully I can explpain:-

Using Visual Studio 2005.
Created a report and assigned a dataset on Data Tab.
Added Table(s) and selected relevant columns.

On Layout Tab, have added a table.
Column 1 = Fields!Supplier.Value
Column 2 =count(Fields!trans_nett.Value)

This gives me the result, but now I need to sort the result; hopefully from within the Layout Tab (Properties? Expression?)

Thanks again

SwePeso
Patron Saint of Lost Yaks

30421 Posts

Posted - 2007-03-28 : 08:43:14

Try nr's approach when creating the dataset.
Don't forget the ORDER BY!

DATASET:

select supplier, count(*) AS num
from tbl
group by supplier
order by count(*) desc, supplier

Peter Larsson
Helsingborg, Sweden

X002548
Not Just a Number

15586 Posts

Posted - 2007-03-28 : 10:05:28

quote:
Originally posted by Shodman

I'm not into using syntax.

I don't know what that even means

Brett

8-)

Hint: Want your questions answered fast? Follow the direction in this link
http://weblogs.sqlteam.com/brettk/archive/2005/05/25/5276.aspx

Add yourself!
http://www.frappr.com/sqlteam

harsh_athalye
Master Smack Fu Yak Hacker

5581 Posts

Posted - 2007-03-28 : 10:15:26

My guess is that he is asking if this can be done from the front-end (reporting services maybe) by clicking some toolbar button or setting some property? He doesn't want to do it by writing query.

Harsh Athalye
India.
"The IMPOSSIBLE is often UNTRIED"

joblenis
Starting Member

29 Posts

Posted - 2007-03-28 : 11:01:58

I have a quick question to add on this topic. I too am new. I want to order the results by the largest count, not by the column but every time I GROUP BY count(1) I get an error, and I cant get ORDER BY to work at all. On top of this, once I get the ordering done (by the count(1)) I want to show just the top 50 results, but im sure I'll be able to add a TOP 50 in the SELECT TO statement. Here is what I have so far.

(
SELECT [csUriStem], count(1)
FROM [IISLOG_REPORTS].[dbo].[tblIISLog_Study_Raaft]
GROUP BY [csUriStem]
)

Thanks guys

SwePeso
Patron Saint of Lost Yaks

30421 Posts

Posted - 2007-03-28 : 11:05:30

SELECT top 50 [csUriStem], count(*)
FROM [IISLOG_REPORTS].[dbo].[tblIISLog_Study_Raaft]
GROUP BY [csUriStem]
order by 2 desc

Peter Larsson
Helsingborg, Sweden

joblenis
Starting Member

29 Posts

Posted - 2007-03-28 : 11:19:28

Thank you, that works great. So the 'ORDER BY 2' just tells it to order by the 2nd value I specified, which was the count? And should I always use count(*) in the future, I usually use count(1).. what is the difference? Thanks for the quick reply too.

SwePeso
Patron Saint of Lost Yaks

30421 Posts

Posted - 2007-03-28 : 11:22:47

ORDER BY 2 denotes ordering by the second column in the resultset.

Peter Larsson
Helsingborg, Sweden

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources