distinct - 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

Transact-SQL (2000)

distinct

Author

Topic

MarioK
Starting Member

20 Posts

Posted - 2005-03-21 : 13:21:23

please somebody show me how to correct and make its work of this query. Thanks

SELECT distinct callplacedtime
from i3_OCWorkflow_ch0
order by callplacedtime desc

my result:
2005-03-21 08:28:23.000
2005-03-21 08:27:12.000
2005-03-18 08:25:32.000
2005-03-18 08:25:30.000
2005-03-16 08:25:29.000
2005-03-16 08:25:26.000
2005-03-01 08:25:06.000
2005-03-01 08:24:57.000...

...
more

expect result:
i want the result look like this
2005-03-21
2005-03-18
2005-03-16
2005-03-01

tkizer
Almighty SQL Goddess

38200 Posts

Posted - 2005-03-21 : 13:45:26

You need to use CONVERT with a style.

SELECT DISTINCT CONVERT(varchar(10), callplacedtime, 110)
FROM...

Tara

MarioK
Starting Member

20 Posts

Posted - 2005-03-21 : 14:59:31

THANK TARA VERY MUCH
its work!!! for the DISTINCT,
But
when i use the ORDER BY callplacedtime desc, it doesn't work. please show me how to fix the problems. thanks

here is the result:
03-16-2005
03-14-2005
03-07-2005
03-18-2005
03-08-2005
03-21-2005
03-10-2005
03-03-2005
03-11-2005
03-17-2005
03-15-2005
03-04-2005
03-09-2005
03-02-2005

tkizer
Almighty SQL Goddess

38200 Posts

Posted - 2005-03-21 : 15:05:44

Please post the query that isn't working. These both work for me:

SELECT distinct CONVERT(varchar(10), callplacedtime, 110)
from i3_OCWorkflow_ch0
order by CONVERT(varchar(10), callplacedtime, 110) desc

SELECT CONVERT(varchar(10), callplacedtime, 110)
from i3_OCWorkflow_ch0
order by callplacedtime desc

Tara

jsmith8858
Dr. Cross Join

7423 Posts

Posted - 2005-03-21 : 15:09:24

converting to a varchar will screw up your sorting as you've noticed. The solution is do not convert anything -- leave your values as datetime's, but simply strip off the time portion.

The trick to do this easily is by doing something like this:


select distinct DateOnly
from
(
  select dateadd(dd,datediff(dd,0,callplacedtime),0) as DateOnly
  from YourTable
) a
order by DateOnly

The funky DateAdd(DateDiff) formula effectively removes the time portion of the date w/o the need to convert things to other datatypes.

- Jeff

tkizer
Almighty SQL Goddess

38200 Posts

Posted - 2005-03-21 : 15:22:56

I just can't use this: dateadd(dd,datediff(dd,0,callplacedtime),0) as DateOnly. For readability sake, I use CONVERT with a style. Then if I really need to use it as a datetime, I do an extra CONVERT back to datetime.

I do use your derived table approach so that I don't have to continuously write out the conversion for the GROUP BYs, ORDER BYs, etc.

Tara

MarioK
Starting Member

20 Posts

Posted - 2005-03-21 : 15:26:06

Finally, I got the right answer from your help. thanks Tara again.

SELECT distinct CONVERT(varchar(10), callplacedtime, 110)
from i3_OCWorkflow_ch0
order by CONVERT(varchar(10), callplacedtime, 110) desc

1)if you using this query, then the result only showed the ORDER BY

SELECT CONVERT(varchar(10), callplacedtime, 110)
from i3_OCWorkflow_ch0
order by callplacedtime desc

2) if i type DISTINCT then i got the error below

SELECT distinct CONVERT(varchar(10), callplacedtime, 110)
from i3_OCWorkflow_ch0
order by callplacedtime desc

Server: Msg 145, Level 15, State 1, Line 1
ORDER BY items must appear in the select list if SELECT DISTINCT is specified.

Thanks

jsmith8858
Dr. Cross Join

7423 Posts

Posted - 2005-03-21 : 15:41:01

quote:
Originally posted by tduggan

I just can't use this: dateadd(dd,datediff(dd,0,callplacedtime),0) as DateOnly. For readability sake, I use CONVERT with a style. Then if I really need to use it as a datetime, I do an extra CONVERT back to datetime.

I do use your derived table approach so that I don't have to continuously write out the conversion for the GROUP BYs, ORDER BYs, etc.

Tara

just create a simple UDF to do the job. If your data is a date, then you should keep it that way.

- Jeff

X002548
Not Just a Number

15586 Posts

Posted - 2005-03-21 : 16:10:13

[code]
USE Northwind
GO
SELECT New_ShippedDate FROM (
SELECT DISTINCT CONVERT(varchar(10),ShippedDate,110) AS New_ShippedDate, ShippedDate
FROM Orders) AS xxx
ORDER BY ShippedDate

[/code]

Brett

8-)

jsmith8858
Dr. Cross Join

7423 Posts

Posted - 2005-03-21 : 16:14:38

Brett -- none of those dates have times. if the data had times as well, you wouldn't get distinct results.

- Jeff

tkizer
Almighty SQL Goddess

38200 Posts

Posted - 2005-03-21 : 16:21:13

I should mention style 112 should be used instead of 110. Year must come first.

Tara

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources