order by with 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 2008 Forums

Transact-SQL (2008)

order by with distinct

Author

Topic

2revup
Posting Yak Master

112 Posts

Posted - 2013-06-04 : 20:31:58

Guys, I am having so much grief with a query that I need to do an order by on because it has distinct which it must maintain.

Here is the query

select
Distinct UserLogin
, CaseID
, Supervisor
, Location
,convert(nvarchar(2),DATEPART(wk,Creation_Date))+ '/' + convert(nvarchar(4),DATEPART(yy,Creation_Date)) as YearWeek
from Correspondences_All
where creation_date
BETWEEN '2013/01/01' AND '2013/05/05'
and UserLogin in ('xxxxxx')

order by case when DATEPART(ww,Creation_Date)+ DATEPART(yy,Creation_Date) like '%[^0-9]%' then 1 else 0 end, DATEPART(ww,Creation_Date)+ DATEPART(yy,Creation_Date),
case when UserLogin IS NULL then 1 else 0 end,
case when caseID IS NULL then 1 else 0 end,
case when Supervisor IS NULL then 1 else 0 end,
case when Location IS NULL then 1 else 0 end,
case when yearweek IS NULL then 1 else 0 end

returns error: ORDER BY items must appear in the select list if SELECT DISTINCT is specified.

If I try

order by case when DATEPART(ww,Creation_Date)+ DATEPART(yy,Creation_Date) like '%[^0-9]%' then 1 else 0 end, DATEPART(ww,Creation_Date)+ DATEPART(yy,Creation_Date),
1,2,3,4,5

Still the same error, what do I need to do to get this to work and order my YearWeek?

visakh16
Very Important crosS Applying yaK Herder

52326 Posts

Posted - 2013-06-04 : 23:47:42

can you give some sample data and explain how you want sorting to happen?

------------------------------------------------------------------------------------------------------
SQL Server MVP
http://visakhm.blogspot.com/
https://www.facebook.com/VmBlogs

2revup
Posting Yak Master

112 Posts

Posted - 2013-06-05 : 00:18:04

Sure:
CREATE TABLE [dbo].[Correspondences_All](
[UserLogin] [varchar](max) NULL,
[comm_id] [bigint] NULL,
[CaseID] [int] NULL,
[Creation_Date] [datetime] NULL,
[Supervisor] [varchar](max) NULL,
[Location] [varchar](max) NULL
) ON [PRIMARY]

insert into Correspondences_All
SELECT 'xxxxxx',5537552783,512643581,2013-03-28,'Brad','AUS'
UNION ALL SELECT 'xxxxxx',5537552784,513611771,2013-04-16,'Brad','AUS'
UNION ALL SELECT 'xxxxxx',5537552785,517407871,2013-02-20,'Brad','AUS'
UNION ALL SELECT 'xxxxxx',5537552786,517418321,2013-02-18,'Brad','AUS'
UNION ALL SELECT 'xxxxxx',5537552787,517436401,2013-02-16,'Brad','AUS'
UNION ALL SELECT 'xxxxxx',5537552788,517572141,2013-03-05,'Brad','AUS'
UNION ALL SELECT 'xxxxxx',5537552789,517615641,2013-02-28,'Brad','AUS'
UNION ALL SELECT 'xxxxxx',5537552790,517881211,2013-02-27,'Brad','AUS'
UNION ALL SELECT 'xxxxxx',5537552791,517904061,2013-02-23,'Brad','AUS'
UNION ALL SELECT 'xxxxxx',5537552792,517918001,2013-03-13,'Brad','AUS'
UNION ALL SELECT 'xxxxxx',5537552793,517918001,2013-02-25,'Brad','AUS'

I am as you can see calulating what the yy and mm are, and then all I really need to is display the data based on the weekyear as you see in the query.

visakh16
Very Important crosS Applying yaK Herder

52326 Posts

Posted - 2013-06-05 : 00:25:53

[code]
select UserLogin
, CaseID
, Supervisor
, Location
,YearWeek
from
(
select
Distinct UserLogin
, CaseID
, Supervisor
, Location
,convert(nvarchar(2),DATEPART(wk,Creation_Date))+ '/' + convert(nvarchar(4),DATEPART(yy,Creation_Date)) as YearWeek
from Correspondences_All
where creation_date
BETWEEN '2013/01/01' AND '2013/05/05'
and UserLogin in ('xxxxxx')
)t
order by DATEPART(yy,Creation_Date) * 100 + CAST(RIGHT('00' + DATENAME(ww,Creation_Date),2) AS int),
case when UserLogin IS NULL then 1 else 0 end,
case when caseID IS NULL then 1 else 0 end,
case when Supervisor IS NULL then 1 else 0 end,
case when Location IS NULL then 1 else 0 end,
case when yearweek IS NULL then 1 else 0 end
[/code]

------------------------------------------------------------------------------------------------------
SQL Server MVP
http://visakhm.blogspot.com/
https://www.facebook.com/VmBlogs

bandi
Master Smack Fu Yak Hacker

2242 Posts

Posted - 2013-06-05 : 00:44:03

quote:

Originally posted by visakh16


select UserLogin
, CaseID
, Supervisor
, Location
,YearWeek
from
(
select 
Distinct UserLogin
, CaseID
, Supervisor
, Location
,convert(nvarchar(2),DATEPART(wk,Creation_Date))+ '/' + convert(nvarchar(4),DATEPART(yy,Creation_Date)) as YearWeek
,DATEPART(yy,Creation_Date) * 100 + CAST(RIGHT('00' + DATENAME(ww,Creation_Date),2) AS int) YearWk
from Correspondences_All 
where creation_date
BETWEEN '2013/01/01' AND '2013/05/05'
and UserLogin in ('xxxxxx')
)t
order by YearWk,
case when UserLogin IS NULL then 1 else 0 end, 
case when caseID IS NULL then 1 else 0 end, 
case when Supervisor IS NULL then 1 else 0 end, 
case when Location IS NULL then 1 else 0 end,
case when yearweek IS NULL then 1 else 0 end

------------------------------------------------------------------------------------------------------
SQL Server MVP
http://visakhm.blogspot.com/
https://www.facebook.com/VmBlogs

Visakh, Your query will give error because Creation_Date is not available to outer query...

--
Chandu

visakh16
Very Important crosS Applying yaK Herder

52326 Posts

Posted - 2013-06-05 : 00:53:48

ah ...nice catch


select UserLogin
, CaseID
, Supervisor
, Location
,YearWeek
from
(
select 
Distinct UserLogin
, CaseID
, Supervisor
, Location
,Creation_Date
,convert(nvarchar(2),DATEPART(wk,Creation_Date))+ '/' + convert(nvarchar(4),DATEPART(yy,Creation_Date)) as YearWeek
from Correspondences_All 
where creation_date
BETWEEN '2013/01/01' AND '2013/05/05'
and UserLogin in ('xxxxxx')
)t
order by DATEPART(yy,Creation_Date) * 100 + CAST(RIGHT('00' + DATENAME(ww,Creation_Date),2) AS int),
case when UserLogin IS NULL then 1 else 0 end, 
case when caseID IS NULL then 1 else 0 end, 
case when Supervisor IS NULL then 1 else 0 end, 
case when Location IS NULL then 1 else 0 end,
case when yearweek IS NULL then 1 else 0 end

------------------------------------------------------------------------------------------------------
SQL Server MVP
http://visakhm.blogspot.com/
https://www.facebook.com/VmBlogs

bandi
Master Smack Fu Yak Hacker

2242 Posts

Posted - 2013-06-05 : 00:57:24

quote:

Originally posted by visakh16

ah ...nice catch


select UserLogin
, CaseID
, Supervisor
, Location
,YearWeek
from
(
select 
Distinct UserLogin
, CaseID
, Supervisor
, Location
,Creation_Date
,convert(nvarchar(2),DATEPART(wk,Creation_Date))+ '/' + convert(nvarchar(4),DATEPART(yy,Creation_Date)) as YearWeek
from Correspondences_All 
where creation_date
BETWEEN '2013/01/01' AND '2013/05/05'
and UserLogin in ('xxxxxx')
)t
order by DATEPART(yy,Creation_Date) * 100 + CAST(RIGHT('00' + DATENAME(ww,Creation_Date),2) AS int),
case when UserLogin IS NULL then 1 else 0 end, 
case when caseID IS NULL then 1 else 0 end, 
case when Supervisor IS NULL then 1 else 0 end, 
case when Location IS NULL then 1 else 0 end,
case when yearweek IS NULL then 1 else 0 end

------------------------------------------------------------------------------------------------------
SQL Server MVP
http://visakhm.blogspot.com/
https://www.facebook.com/VmBlogs

If we include that column in Inner Query, how can we get YearWeek-wise distinct records...
I think OP wants to remove duplicate YearWeeks...
Check the green part in my earlier post ( I thought like that)

--
Chandu

visakh16
Very Important crosS Applying yaK Herder

52326 Posts

Posted - 2013-06-05 : 01:04:02

then why duplicating?
isnt this enough?


select UserLogin
, CaseID
, Supervisor
, Location
,YearWeek
from
(
select 
Distinct UserLogin
, CaseID
, Supervisor
, Location
,DATEPART(yy,Creation_Date) * 100 + CAST(RIGHT('00' + DATENAME(ww,Creation_Date),2) AS int) as YearWeek
from Correspondences_All 
where creation_date
BETWEEN '2013/01/01' AND '2013/05/05'
and UserLogin in ('xxxxxx')
)t
order by YearWeek,
case when UserLogin IS NULL then 1 else 0 end, 
case when caseID IS NULL then 1 else 0 end, 
case when Supervisor IS NULL then 1 else 0 end, 
case when Location IS NULL then 1 else 0 end,
case when yearweek IS NULL then 1 else 0 end

------------------------------------------------------------------------------------------------------
SQL Server MVP
http://visakhm.blogspot.com/
https://www.facebook.com/VmBlogs

visakh16
Very Important crosS Applying yaK Herder

52326 Posts

Posted - 2013-06-05 : 01:09:47

or even this?


SELECT UserLogin
, CaseID
, Supervisor
, Location
,convert(nvarchar(2),DATEPART(wk,Creation_Date))+ '/' + convert(nvarchar(4),DATEPART(yy,Creation_Date))
FROM
(
select 
 UserLogin
, CaseID
, Supervisor
, Location
,Creation_Date
,ROW_NUMBER() OVER (PARTITION BY DATEPART(yy,Creation_Date),DATEPART(wk,Creation_Date) ORDER BY Creation_Date DESC) AS Seq
from Correspondences_All 
where creation_date
BETWEEN '2013/01/01' AND '2013/05/05'
and UserLogin in ('xxxxxx')
)t
WHERE Seq=1
ORDER BY DATEPART(yy,Creation_Date) * 100 + CAST(RIGHT('00' + DATENAME(ww,Creation_Date),2) AS int),
case when UserLogin IS NULL then 1 else 0 end, 
case when caseID IS NULL then 1 else 0 end, 
case when Supervisor IS NULL then 1 else 0 end, 
case when Location IS NULL then 1 else 0 end,
case when yearweek IS NULL then 1 else 0 end

------------------------------------------------------------------------------------------------------
SQL Server MVP
http://visakhm.blogspot.com/
https://www.facebook.com/VmBlogs

bandi
Master Smack Fu Yak Hacker

2242 Posts

Posted - 2013-06-05 : 01:11:10

May be he wants YearWeek Column format as WK/YYYY?

--
Chandu

visakh16
Very Important crosS Applying yaK Herder

52326 Posts

Posted - 2013-06-05 : 01:15:02

quote:
Originally posted by bandi

May be he wants YearWeek Column format as WK/YYYY?

--
Chandu

yep...thats why i included it in final select

But IMHO thats a formatting issue which should be done at front end rather than in SQL

------------------------------------------------------------------------------------------------------
SQL Server MVP
http://visakhm.blogspot.com/
https://www.facebook.com/VmBlogs

2revup
Posting Yak Master

112 Posts

Posted - 2013-06-05 : 02:08:18

A few options to choose from, thanks fellas. You guys ROCK!

bandi
Master Smack Fu Yak Hacker

2242 Posts

Posted - 2013-06-05 : 02:10:15

quote:
Originally posted by 2revup

A few options to choose from, thanks fellas. You guys ROCK!

Welcome

--
Chandu

visakh16
Very Important crosS Applying yaK Herder

52326 Posts

Posted - 2013-06-05 : 02:33:28

quote:
Originally posted by 2revup

A few options to choose from, thanks fellas. You guys ROCK!

welcome

------------------------------------------------------------------------------------------------------
SQL Server MVP
http://visakhm.blogspot.com/
https://www.facebook.com/VmBlogs

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources