Joined: 15 Dec 2002 Posts: 637 Topics: 43 Location: Bengaluru, INDIA
Posted: Wed Feb 01, 2006 12:11 pm Post subject: SQL for ordering columns in 'irregular' collating sequence.
Hi,
Can someone please explain this SQL :
Code:
SELECT DAY_NAME, COL1, COL2 . . .FROM
TXN_TABLE ORDER BY LOCATE(DAY_NAME,'SUNMONTUEWEDTHUFRISAT');
to sort the names of days in the chronological sequence rather than the
alphabetic sequence.
My confusion is with the LOCATE function. This will return values that will change with every value in the said column. So, how does the ORDER BY actually happen?
I am not sure, I follow the explanation given in the article.
Quote:
"So, in our example SQL, LOCATE finds the position of the DAY_NAME value within the string 'SUNMONTUEWEDTHUFRISAT', and returns the integer value of that position. So, if DAY_NAME is WED, the LOCATE function in the previously mentioned SQL statement returns 10. (Note: Some other database systems have a similar function called INSTR.) Sunday would return 1, Monday 4, Tuesday 7, Wednesday 10, Thursday 13, Friday 16, and Saturday 19. This means that our results would be in the order we require."
_________________ ALL opinions are welcome.
Debugging tip:
When you have eliminated all which is impossible, then whatever remains, however improbable, must be the truth.
-- Sherlock Holmes.
Joined: 03 Jan 2003 Posts: 550 Topics: 23 Location: Michigan, USA
Posted: Wed Feb 01, 2006 2:47 pm Post subject:
There is a column called DAY_NAME with possible values of SUN, MON, TUE, WED, THU, FRI and SAT.
ORDER BY DAY_NAME would order it alphabetically i.e. FRI, MON, SAT, SUN, THU, TUE and WED.
To order it by day of week, use the LOCATE function as you described. The value for SUN would always be 1, MON would always be 4, TUE would be 7 and so on.
Joined: 15 Dec 2002 Posts: 637 Topics: 43 Location: Bengaluru, INDIA
Posted: Wed Feb 01, 2006 11:12 pm Post subject:
Bithead,
Bithead wrote:
There is a column called DAY_NAME with possible values of SUN, MON, TUE, WED, THU, FRI and SAT.
ORDER BY DAY_NAME would order it alphabetically i.e. FRI, MON, SAT, SUN, THU, TUE and WED.
To order it by day of week, use the LOCATE function as you described. The value for SUN would always be 1, MON would always be 4, TUE would be 7 and so on.
I understood that.
But, my confusion is with the ORDER BY LOCATE(...) part. As Kolusu, pointed the ORDER BY is dynamic in nature... _________________ ALL opinions are welcome.
Debugging tip:
When you have eliminated all which is impossible, then whatever remains, however improbable, must be the truth.
-- Sherlock Holmes.
Joined: 26 Nov 2002 Posts: 12378 Topics: 75 Location: San Jose
Posted: Thu Feb 02, 2006 8:16 am Post subject:
Quote:
I ran a quick test and it worked. (DB2 v7)
Bithead,
Did you run the query which Cogito has provided in the first post? I don't think Day_name function is supported in DB2 unless you have the schema DSN8.
Joined: 15 Dec 2002 Posts: 637 Topics: 43 Location: Bengaluru, INDIA
Posted: Thu Feb 02, 2006 8:57 am Post subject:
Kolusu,
Quote:
Did you run the query which Cogito has provided in the first post? I don't think Day_name function is supported in DB2 unless you have the schema DSN8.
The DAY_NAME is a column above not a function. _________________ ALL opinions are welcome.
Debugging tip:
When you have eliminated all which is impossible, then whatever remains, however improbable, must be the truth.
-- Sherlock Holmes.
Joined: 03 Dec 2002 Posts: 579 Topics: 1 Location: Iowa, USA
Posted: Thu Feb 02, 2006 12:05 pm Post subject:
Cogito,
Are you still confused?
Your ORDER BY statement specifies the column DAY_NAME. Before sorting, the column has a LOCATE function performed on it where the values are converted to the offset into the list 'SUNMON.....SAT'. _________________ Regards,
Bill Dennis
Disclaimer: My comments on this foorum are my own and do not represent the opinions or suggestions of any other person or business entity.
You cannot post new topics in this forum You cannot reply to topics in this forum You cannot edit your posts in this forum You cannot delete your posts in this forum You cannot vote in polls in this forum
FAQ
Search
Quick Manuals
Register
Profile
Log in to check your private messages
Log in
