group by and 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

SQL Server 2000 Forums

SQL Server Development (2000)

group by and order by

Author

Topic

querybest
Starting Member

22 Posts

Posted - 2005-08-11 : 14:32:10

I know they can be used at the same query in normal..

I am selecting records by group but I need to query them specific query.

like ".... group by x,y order by date desc"

performance is important for it..because the table I am using has more than 10.000 records and it will be called once 30 minutes.

spirit1
Cybernetic Yak Master

11752 Posts

Posted - 2005-08-11 : 14:44:44

so what is your question??

Go with the flow & have fun! Else fight the flow

querybest
Starting Member

22 Posts

Posted - 2005-08-11 : 17:32:23

I get the following error when I use "order by id desc"

(and I define Id column in group by (group by x,y,id) ,esults are not what I want

[Microsoft][ODBC SQL Server Driver][SQL Server]Column name 'Temp.id' is invalid in the ORDER BY clause because it is not contained in either an aggregate function or the GROUP BY clause.

AjarnMark
SQL Slashing Gunting Master

3246 Posts

Posted - 2005-08-11 : 18:13:08

You need to reference the ID column in your ORDER BY clause the same way it is referenced in the GROUP BY clause.

---------------------------
EmeraldCityDomains.com

querybest
Starting Member

22 Posts

Posted - 2005-08-11 : 19:20:22

quote:
Originally posted by querybest

(and I define Id column in group by (group by x,y,id) ,esults are not what I want

I did a mistake above..

I wanted to say
"If I define ID column in group by, the results are not what I want"

because ID is autonumber..

in this respect when I define ID in group by, "select * from table order by id desc" is the same with "select x,y,id from table group by x,y,id order by id desc"

help please
thank you

madhivanan
Premature Yak Congratulator

22864 Posts

Posted - 2005-08-12 : 01:19:44

Can you post some sample data and the result you want?

Madhivanan

Failing to plan is Planning to fail

querybest
Starting Member

22 Posts

Posted - 2005-08-12 : 01:52:28

[code]ID Date Name
----------------------
1 08/11/05 Mark
2 08/11/05 Alice
3 07/11/05 Mark
4 06/11/05 Tom
5 01/11/05 Alice
6 01/11/05 Alice
7 01/11/05 Alice
8 01/11/05 Alice

I want to get them like below

ID Date Name
----------------------
8 01/11/05 Alice
4 06/11/05 Tom
3 07/11/05 Mark
2 08/11/05 Alice
1 08/11/05 Mark[/code]

madhivanan
Premature Yak Congratulator

22864 Posts

Posted - 2005-08-12 : 02:13:15

See if this works

Select max(id),date,name from yourTable group by date,name
order by 1 desc

Madhivanan

Failing to plan is Planning to fail

querybest
Starting Member

22 Posts

Posted - 2005-08-12 : 03:44:42

thank you,it works.

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources