Styjun

Cold is to Refrigerator as warm is to?

How to market an anarchic city as a tourism spot to people living in civilized areas?

How to politely respond to generic emails requesting a PhD/job in my lab? Without wasting too much time

Why use gamma over alpha radiation?

When is phishing education going too far?

The following signatures were invalid: EXPKEYSIG 1397BC53640DB551

If A makes B more likely then B makes A more likely"

What would be Julian Assange's expected punishment, on the current English criminal law?

How did passengers keep warm on sail ships?

Communication vs. Technical skills ,which is more relevant for today's QA engineer positions?

Are my PIs rude or am I just being too sensitive?

How to pour concrete for curved walkway to prevent cracking?

Mortgage adviser recommends a longer term than necessary combined with overpayments

Can I throw a sword that doesn't have the Thrown property at someone?

Stop battery usage [Ubuntu 18]

Fishing simulator

Who can trigger ship-wide alerts in Star Trek?

Need a suitable toxic chemical for a murder plot in my novel

Limit for e and 1/e

How many spell slots should a Fighter 11/Ranger 9 have?

Is there folklore associating late breastfeeding with low intelligence and/or gullibility?

Keep going mode for require-package

How is simplicity better than precision and clarity in prose?

What kind of display is this?

Redshift - Sorting data by week

.everyoneloves__top-leaderboard:empty,.everyoneloves__mid-leaderboard:empty,.everyoneloves__bot-mid-leaderboard:empty height:90px;width:728px;box-sizing:border-box;

1

I am trying to sort some data in Redshift by week.

select CONCAT(CONCAT('Week', ' '), 
EXTRACT(WEEK FROM sale_date::date + '1 day'::interval)) as date,count(*) as total 
from sales 
where sale_date between ('2019-03-22' - 30) and '2019-03-22' 
group by EXTRACT(WEEK FROM sale_date::date + '1 day'::interval)
order by 1 asc;

The above query works fine however the output is as below:

Week 10, 10
Week 11, 20
Week 12, 3
Week 7, 12
Week 8, 3
Week 9, 5

How could I modify the above query such that the output is as below:

Week 7, 12
Week 8, 3
Week 9, 5
Week 10, 10
Week 11, 20
Week 12, 3

sql sql-order-by amazon-redshift

share|improve this question

edited Mar 23 at 7:52

Barbaros Özhan

15.1k71634

asked Mar 22 at 7:20

scott martinscott martin

1378

add a comment | 

1

I am trying to sort some data in Redshift by week.

select CONCAT(CONCAT('Week', ' '), 
EXTRACT(WEEK FROM sale_date::date + '1 day'::interval)) as date,count(*) as total 
from sales 
where sale_date between ('2019-03-22' - 30) and '2019-03-22' 
group by EXTRACT(WEEK FROM sale_date::date + '1 day'::interval)
order by 1 asc;

The above query works fine however the output is as below:

Week 10, 10
Week 11, 20
Week 12, 3
Week 7, 12
Week 8, 3
Week 9, 5

How could I modify the above query such that the output is as below:

Week 7, 12
Week 8, 3
Week 9, 5
Week 10, 10
Week 11, 20
Week 12, 3

sql sql-order-by amazon-redshift

share|improve this question

edited Mar 23 at 7:52

Barbaros Özhan

15.1k71634

asked Mar 22 at 7:20

scott martinscott martin

1378

add a comment | 

I am trying to sort some data in Redshift by week.

select CONCAT(CONCAT('Week', ' '), 
EXTRACT(WEEK FROM sale_date::date + '1 day'::interval)) as date,count(*) as total 
from sales 
where sale_date between ('2019-03-22' - 30) and '2019-03-22' 
group by EXTRACT(WEEK FROM sale_date::date + '1 day'::interval)
order by 1 asc;

The above query works fine however the output is as below:

Week 10, 10
Week 11, 20
Week 12, 3
Week 7, 12
Week 8, 3
Week 9, 5

How could I modify the above query such that the output is as below:

Week 7, 12
Week 8, 3
Week 9, 5
Week 10, 10
Week 11, 20
Week 12, 3

sql sql-order-by amazon-redshift

share|improve this question

edited Mar 23 at 7:52

Barbaros Özhan

15.1k71634

asked Mar 22 at 7:20

scott martinscott martin

1378

select CONCAT(CONCAT('Week', ' '), 
EXTRACT(WEEK FROM sale_date::date + '1 day'::interval)) as date,count(*) as total 
from sales 
where sale_date between ('2019-03-22' - 30) and '2019-03-22' 
group by EXTRACT(WEEK FROM sale_date::date + '1 day'::interval)
order by 1 asc;

Week 10, 10
Week 11, 20
Week 12, 3
Week 7, 12
Week 8, 3
Week 9, 5

Week 7, 12
Week 8, 3
Week 9, 5
Week 10, 10
Week 11, 20
Week 12, 3

share|improve this question

edited Mar 23 at 7:52

Barbaros Özhan

15.1k71634

asked Mar 22 at 7:20

scott martinscott martin

1378

share|improve this question

edited Mar 23 at 7:52

Barbaros Özhan

15.1k71634

asked Mar 22 at 7:20

scott martinscott martin

1378

edited Mar 23 at 7:52

Barbaros Özhan

15.1k71634

edited Mar 23 at 7:52

Barbaros Özhan

15.1k71634

asked Mar 22 at 7:20

scott martinscott martin

1378

asked Mar 22 at 7:20

scott martinscott martin

1378

1 Answer
1

active

oldest

votes

2

you need to cat your extracted week value to a numeric type for order by statement. So convert

order by 1 asc to

order by cast(EXTRACT(WEEK FROM sale_date::date + '1 day'::interval) as integer)

share|improve this answer

answered Mar 22 at 7:39

Barbaros ÖzhanBarbaros Özhan

15.1k71634

• 
  
  
  

  
  

  
  
  
  
  
  

  
  
  thank you that helped
  
  – scott martin
  Mar 22 at 11:44
  

  
  
  
  


• 
  
  
  

  
  

  
  
  
  
  
  

  
  
  @scottmartin you're welcome.
  
  – Barbaros Özhan
  Mar 22 at 12:39
  

  
  
  
  

add a comment | 

Your Answer

StackExchange.ifUsing("editor", function ()
StackExchange.using("externalEditor", function ()
StackExchange.using("snippets", function ()
StackExchange.snippets.init();
);
);
, "code-snippets");

StackExchange.ready(function()
var channelOptions =
tags: "".split(" "),
id: "1"
;
initTagRenderer("".split(" "), "".split(" "), channelOptions);

StackExchange.using("externalEditor", function()
// Have to fire editor after snippets, if snippets enabled
if (StackExchange.settings.snippets.snippetsEnabled)
StackExchange.using("snippets", function()
createEditor();
);

else
createEditor();

);

function createEditor()
StackExchange.prepareEditor(
heartbeatType: 'answer',
autoActivateHeartbeat: false,
convertImagesToLinks: true,
noModals: true,
showLowRepImageUploadWarning: true,
reputationToPostImages: 10,
bindNavPrevention: true,
postfix: "",
imageUploader:
brandingHtml: "Powered by u003ca class="icon-imgur-white" href="https://imgur.com/"u003eu003c/au003e",
contentPolicyHtml: "User contributions licensed under u003ca href="https://creativecommons.org/licenses/by-sa/3.0/"u003ecc by-sa 3.0 with attribution requiredu003c/au003e u003ca href="https://stackoverflow.com/legal/content-policy"u003e(content policy)u003c/au003e",
allowUrls: true
,
onDemand: true,
discardSelector: ".discard-answer"
,immediatelyShowMarkdownHelp:true
);



);

draft saved

draft discarded

Sign up or log in

StackExchange.ready(function ()
StackExchange.helpers.onClickDraftSave('#login-link');
);

Sign up using Google

Sign up using Facebook

Sign up using Email and Password

Post as a guest

Name

Email

Required, but never shown

StackExchange.ready(
function ()
StackExchange.openid.initPostLogin('.new-post-login', 'https%3a%2f%2fstackoverflow.com%2fquestions%2f55294674%2fredshift-sorting-data-by-week%23new-answer', 'question_page');

);

Post as a guest

Name

Email

Required, but never shown

2

you need to cat your extracted week value to a numeric type for order by statement. So convert

order by 1 asc to

order by cast(EXTRACT(WEEK FROM sale_date::date + '1 day'::interval) as integer)

share|improve this answer

answered Mar 22 at 7:39

Barbaros ÖzhanBarbaros Özhan

15.1k71634

• 
  
  
  

  
  

  
  
  
  
  
  

  
  
  thank you that helped
  
  – scott martin
  Mar 22 at 11:44
  

  
  
  
  


• 
  
  
  

  
  

  
  
  
  
  
  

  
  
  @scottmartin you're welcome.
  
  – Barbaros Özhan
  Mar 22 at 12:39
  

  
  
  
  

add a comment | 

2

you need to cat your extracted week value to a numeric type for order by statement. So convert

order by 1 asc to

order by cast(EXTRACT(WEEK FROM sale_date::date + '1 day'::interval) as integer)

share|improve this answer

answered Mar 22 at 7:39

Barbaros ÖzhanBarbaros Özhan

15.1k71634

• 
  
  
  

  
  

  
  
  
  
  
  

  
  
  thank you that helped
  
  – scott martin
  Mar 22 at 11:44
  

  
  
  
  


• 
  
  
  

  
  

  
  
  
  
  
  

  
  
  @scottmartin you're welcome.
  
  – Barbaros Özhan
  Mar 22 at 12:39
  

  
  
  
  

add a comment | 

you need to cat your extracted week value to a numeric type for order by statement. So convert

order by 1 asc to

order by cast(EXTRACT(WEEK FROM sale_date::date + '1 day'::interval) as integer)

share|improve this answer

answered Mar 22 at 7:39

Barbaros ÖzhanBarbaros Özhan

15.1k71634

share|improve this answer

answered Mar 22 at 7:39

Barbaros ÖzhanBarbaros Özhan

15.1k71634

answered Mar 22 at 7:39

Barbaros ÖzhanBarbaros Özhan

15.1k71634

answered Mar 22 at 7:39

Barbaros ÖzhanBarbaros Özhan

15.1k71634