order by issue - 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 2005 Forums

Transact-SQL (2005)

order by issue

Author

Topic

soorajtnpki
Posting Yak Master

231 Posts

Posted - 2009-09-02 : 07:45:24

hi all, i am facing an issue for date ordering in a select query. what i return is dates are ordered in their month name basis, for example

like Apr 1 2008
Aug 12 2008
feb 11 2008
jan 23 2008

SELECT left([ORD_DATEPLACED], 11) as date
FROM acct_ordersGeekBro
WHERE
1=1

GROUP BY left([ORD_DATEPLACED], 11)
ORDER BY left([ORD_DATEPLACED], 11) ASC

i need group the records as left([ORD_DATEPLACED], 11) basis also, for getting daywise criteria..

thanx in advance

senthil_nagore
Master Smack Fu Yak Hacker

1007 Posts

Posted - 2009-09-02 : 08:03:35

Try this

select convert(varchar,date_column,9) from table_name
order by convert(varchar,date_column,9)

Senthil.C
------------------------------------------------------
[Microsoft][ODBC SQL Server Driver]Operation canceled

http://senthilnagore.blogspot.com/

soorajtnpki
Posting Yak Master

231 Posts

Posted - 2009-09-02 : 08:12:07

thanx it worked

soorajtnpki
Posting Yak Master

231 Posts

Posted - 2009-09-02 : 08:21:10

but it has still problems, i am using it with other datefunction in front end. now it is not a datetime object..

senthil_nagore
Master Smack Fu Yak Hacker

1007 Posts

Posted - 2009-09-02 : 08:30:58

quote:
Originally posted by soorajtnpki

but it has still problems, i am using it with other datefunction in front end. now it is not a datetime object..

Convert again as a Datatime object in front end.

Senthil.C
------------------------------------------------------
[Microsoft][ODBC SQL Server Driver]Operation canceled

http://senthilnagore.blogspot.com/

madhivanan
Premature Yak Congratulator

22864 Posts

Posted - 2009-09-02 : 08:31:53

what is the datatype of ORD_DATEPLACED?

Madhivanan

Failing to plan is Planning to fail

visakh16
Very Important crosS Applying yaK Herder

52326 Posts

Posted - 2009-09-02 : 08:46:02

i think what you need is

GROUP BY dateadd(dd,datediff(dd,0,[ORD_DATEPLACED]),0)
ORDER BY datediff(dd,0,[ORD_DATEPLACED]) ASC

soorajtnpki
Posting Yak Master

231 Posts

Posted - 2009-09-02 : 09:14:14

quote:
Originally posted by visakh16

i think what you need is

GROUP BY dateadd(dd,datediff(dd,0,[ORD_DATEPLACED]),0)
ORDER BY datediff(dd,0,[ORD_DATEPLACED]) ASC

yes visakh, but i changed as it gives error

GROUP BY dateadd(dd,datediff(dd,0,[ORD_DATEPLACED]),0)
ORDER BY dateadd(dd,datediff(dd,0,[ORD_DATEPLACED]),0) ASC

visakh16
Very Important crosS Applying yaK Herder

52326 Posts

Posted - 2009-09-02 : 09:20:32

quote:
Originally posted by soorajtnpki

quote:
Originally posted by visakh16

i think what you need is

GROUP BY dateadd(dd,datediff(dd,0,[ORD_DATEPLACED]),0)
ORDER BY datediff(dd,0,[ORD_DATEPLACED]) ASC

yes visakh, but i changed as it gives error

GROUP BY dateadd(dd,datediff(dd,0,[ORD_DATEPLACED]),0)
ORDER BY dateadd(dd,datediff(dd,0,[ORD_DATEPLACED]),0) ASC

or you could simply use an alias in select list and add it to order by

soorajtnpki
Posting Yak Master

231 Posts

Posted - 2009-09-02 : 09:27:53

ok,
if we are giving alias also, since it is not in group by list, it gives error,
group by and order by field data should be same, am i correct?

visakh16
Very Important crosS Applying yaK Herder

52326 Posts

Posted - 2009-09-02 : 09:31:16

quote:
Originally posted by soorajtnpki

ok,
if we are giving alias also, since it is not in group by list, it gives error,
group by and order by field data should be same, am i correct?

nope...not necessarily, though you cant use any other non aggregated fields in order by when used with group by

soorajtnpki
Posting Yak Master

231 Posts

Posted - 2009-09-02 : 09:39:04

error is
Column 'acct_ordersGeekBro.ord_datePlaced' is invalid in the select list because it is not contained in either an aggregate function or the GROUP BY clause

madhivanan
Premature Yak Congratulator

22864 Posts

Posted - 2009-09-02 : 09:43:21

SELECT [ORD_DATEPLACED] as date
FROM acct_ordersGeekBro
WHERE
1=1
GROUP BY dateadd(dd,datediff(dd,0,[ORD_DATEPLACED]),0)
ORDER BY dateadd(dd,datediff(dd,0,[ORD_DATEPLACED]),0) ASC

Madhivanan

Failing to plan is Planning to fail

soorajtnpki
Posting Yak Master

231 Posts

Posted - 2009-09-02 : 09:50:37

yes madhi,
i am going with that one.

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources