Order By - 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

Order By

Author

Topic

cutiebo2t
Constraint Violating Yak Guru

256 Posts

Posted - 2008-08-14 : 04:55:55

I have this statement,

SELECT witnesid, COUNT(witnesid) AS COUNT
FROM dbo.CalibSprint
GROUP BY witnesid

I would like them in order using the ID column.

Is that possible?

SwePeso
Patron Saint of Lost Yaks

30421 Posts

Posted - 2008-08-14 : 04:57:52

Which ID column are you referring to?

SELECT witnesid, COUNT(witnesid) AS COUNT
FROM dbo.CalibSprint
GROUP BY witnesid
ORDER BY witnesid

E 12°55'05.25"
N 56°04'39.16"

cutiebo2t
Constraint Violating Yak Guru

256 Posts

Posted - 2008-08-14 : 04:59:49

I have another column as ID. Is not yet included in the statement.

SwePeso
Patron Saint of Lost Yaks

30421 Posts

Posted - 2008-08-14 : 05:03:59

How would we know? You haven't told us your table layout.
And since you refuse to take an advise to read these two topics to make things easier for you,

http://weblogs.sqlteam.com/jeffs/archive/2008/05/13/question-needed-not-answer.aspx
http://weblogs.sqlteam.com/brettk/archive/2005/05/25/5276.aspx

how do you expect us to know what you want? Do you think we are some kind of mind-readers?

SELECT		WitnesID,
		COUNT(*) AS [COUNT]
FROM		dbo.CalibSprint
GROUP BY	WitnesID
ORDER BY	MIN(ID)

This must be the 10th time I have told you to read the two articles above.
Geesh, why don't you read them?

E 12°55'05.25"
N 56°04'39.16"

SwePeso
Patron Saint of Lost Yaks

30421 Posts

Posted - 2008-08-14 : 05:11:13

And reading the two articles above is not enough!
You will have to follow the excellent guidelines too!

E 12°55'05.25"
N 56°04'39.16"

cutiebo2t
Constraint Violating Yak Guru

256 Posts

Posted - 2008-08-14 : 05:24:14

this is not working :(

SELECT TOP 100 PERCENT WitnesID, WitnesD8, COUNT(*) AS [COUNT]
FROM dbo.CalibSprint
GROUP BY WitnesID
ORDER BY MIN(ID)

elancaster
A very urgent SQL Yakette

1208 Posts

Posted - 2008-08-14 : 05:30:06

hmmm... did it perhaps give you an error with word to the effect ... "something in the select clause that's not in the group by..."

...guess what that means?

Em

cutiebo2t
Constraint Violating Yak Guru

256 Posts

Posted - 2008-08-14 : 05:31:43

yes i know what it means..is there a work around for that?

elancaster
A very urgent SQL Yakette

1208 Posts

Posted - 2008-08-14 : 05:32:29

how about putting it in the group by?

Em

khtan
In (Som, Ni, Yak)

17689 Posts

Posted - 2008-08-14 : 05:32:51

what do you want actually ? Post your table DDL, sample data and required result as what Peso has asked earlier. . .

KH
[spoiler]Time is always against us[/spoiler]

SwePeso
Patron Saint of Lost Yaks

30421 Posts

Posted - 2008-08-14 : 05:37:08

Cutiebo2t, you are such an idiot!

Of course my suggestion above works. Try this

DECLARE	@Sample TABLE
	(
		ID INT IDENTITY(1, 1),
		WitnesID INT
	)

INSERT	@Sample
SELECT	1 UNION ALL
SELECT	2 UNION ALL
SELECT	2 UNION ALL
SELECT	1 UNION ALL
SELECT	3 UNION ALL
SELECT	1 UNION ALL
SELECT	3 UNION ALL
SELECT	1 UNION ALL
SELECT	5 UNION ALL
SELECT	5 UNION ALL
SELECT	7

SELECT		WitnesID,
		COUNT(*) AS [Count]
FROM		@Sample
GROUP BY	WitnesID
ORDER BY	MAX(ID)

If you ever care to copy and paste COMPLETE suggestion you actually might learn something.

E 12°55'05.25"
N 56°04'39.16"

SwePeso
Patron Saint of Lost Yaks

30421 Posts

Posted - 2008-08-14 : 05:38:23

quote:
Originally posted by cutiebo2t

this is not working :(

SELECT TOP 100 PERCENT WitnesID, WitnesD8, COUNT(*) AS [COUNT]
FROM dbo.CalibSprint
GROUP BY WitnesID
ORDER BY MIN(ID)

This is not what I suggested, not what you told us!
Where do column WitnesD8 come from?

E 12°55'05.25"
N 56°04'39.16"

jsmith8858
Dr. Cross Join

7423 Posts

Posted - 2008-08-14 : 09:39:26

To understand a little more on GROUP BY and why you must include columns in the group by clause or in an aggregate expression, please read:

http://weblogs.sqlteam.com/jeffs/archive/2007/07/20/60261.aspx

Cutiebot -- I have to ask: are you a student? Or an intern? Or doing volunteer work somewhere with SQL? or are you employed somewhere as a full-time developer?

- Jeff
http://weblogs.sqlteam.com/JeffS

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources