Results 1 to 14 of 14
  1. #1
    Join Date
    Oct 2007
    Location
    Manila, Philippines
    Posts
    132

    Unanswered: how to sort date?

    hi to all,
    i cant sort my date
    can anyone please check my query.
    Code:
    SELECT
    concat_ws('-',YEAR(tbl_transactions.dtestarted),MONTH(tbl_transactions.dtestarted),DAY(tbl_transactions.dtestarted)) as 'Date',
    tbl_transactions.startedby,
    tbl_user.lastname,
    tbl_errordetail.errcode,
    tbl_error.errdesc,
    count(tbl_errordetail.errcode) as 'error count'
    
    
    FROM
    tbl_errordetail
    inner join tbl_error on (tbl_errordetail.errcode = tbl_error.errcode)
    inner join tbl_transactions on (tbl_errordetail.transid = tbl_transactions.transid)
    inner join tbl_user on (tbl_transactions.startedby = tbl_user.userid)
    where tbl_user.lastname = 'barrios'
    
    group by
    'date',
    tbl_transactions.startedby,
    tbl_user.lastname,
    tbl_errordetail.errcode,
    tbl_error.errdesc
    
    order by
    'date' desc,
    tbl_user.lastname
    my date is sorted but as a string

    e.g.
    Code:
    '2008-5-9', '07010047', 'Barrios', 'Typo', 'Typo/Spelling Error', 2
    '2008-5-9', '07010047', 'Barrios', 'UNF', 'UDAC Not Followed', 1
    '2008-5-8', '07010047', 'Barrios', 'INF', 'Instruction Not Followed', 1
    '2008-5-8', '07010047', 'Barrios', 'MS', 'Missing Text/Omission', 2
    '2008-5-8', '07010047', 'Barrios', 'Typo', 'Typo/Spelling Error', 1
    '2008-5-8', '07010047', 'Barrios', 'UNF', 'UDAC Not Followed', 1
    '2008-5-7', '07010047', 'Barrios', 'Typo', 'Typo/Spelling Error', 4
    '2008-5-6', '07010047', 'Barrios', 'Typo', 'Typo/Spelling Error', 4
    '2008-5-5', '07010047', 'Barrios', 'Typo', 'Typo/Spelling Error', 1
    '2008-5-4', '07010047', 'Barrios', 'ME', 'Mechanical Error', 1
    '2008-5-4', '07010047', 'Barrios', 'Typo', 'Typo/Spelling Error', 2
    '2008-5-3', '07010047', 'Barrios', 'INF', 'Instruction Not Followed', 1
    '2008-5-3', '07010047', 'Barrios', 'Typo', 'Typo/Spelling Error', 6
    '2008-5-22', '07010047', 'Barrios', 'Typo', 'Typo/Spelling Error', 1
    '2008-5-22', '07010047', 'Barrios', 'UNF', 'UDAC Not Followed', 1
    '2008-5-21', '07010047', 'Barrios', 'INF', 'Instruction Not Followed', 1
    '2008-5-21', '07010047', 'Barrios', 'ME', 'Mechanical Error', 1
    '2008-5-21', '07010047', 'Barrios', 'Typo', 'Typo/Spelling Error', 1
    '2008-5-20', '07010047', 'Barrios', 'INF', 'Instruction Not Followed', 1
    '2008-5-20', '07010047', 'Barrios', 'ME', 'Mechanical Error', 2
    '2008-5-2', '07010047', 'Barrios', 'ME', 'Mechanical Error', 2
    '2008-5-17', '07010047', 'Barrios', 'INF', 'Instruction Not Followed', 1
    '2008-5-16', '07010047', 'Barrios', 'Typo', 'Typo/Spelling Error', 4
    '2008-5-15', '07010047', 'Barrios', 'IBN', 'Incorrect Business Name', 1
    '2008-5-15', '07010047', 'Barrios', 'ME', 'Mechanical Error', 1
    '2008-5-15', '07010047', 'Barrios', 'Typo', 'Typo/Spelling Error', 1
    im searching in the in the internet on converting string to date, or sorting date as numeric, but i cant find any.

    thanks
    Take Nothing But Pictures;
    Leave Nothing But Footprints;
    Kill Nothing But Time;

  2. #2
    Join Date
    Nov 2004
    Location
    out on a limb
    Posts
    13,692
    Provided Answers: 59
    so store your date as a DATETIME datatype
    always choose the most appropriate datatype for the type of data, unless you are forced by other constraints

    so store a date as a date, and integer as one of the integer types....
    I'd rather be riding on the Tiger 800 or the Norton

  3. #3
    Join Date
    Oct 2007
    Location
    Manila, Philippines
    Posts
    132
    or to sort a string as a numeric?

    e.g
    Code:
    1
    10
    100
    123
    150
    20
    26
    205
    to
    Code:
    1
    10
    20
    26
    100
    123
    150
    205
    Take Nothing But Pictures;
    Leave Nothing But Footprints;
    Kill Nothing But Time;

  4. #4
    Join Date
    Apr 2002
    Location
    Toronto, Canada
    Posts
    20,002
    would you kindly run this query please:
    Code:
    SHOW CREATE TABLE tbl_transactions
    rudy.ca | @rudydotca
    Buy my SitePoint book: Simply SQL

  5. #5
    Join Date
    Nov 2004
    Location
    out on a limb
    Posts
    13,692
    Provided Answers: 59
    Quote Originally Posted by homer.favenir
    or to sort a string as a numeric?

    e.g
    Code:
    1
    10
    100
    123
    150
    20
    26
    205
    to
    Code:
    1
    10
    20
    26
    100
    123
    150
    205
    as said before store numbers as number, dates as dates strings as strings and let the SQL engine do the sorting.
    failing that YOU are going to have to do the work so YOU will have to store dates with leading zeros of spaces
    eg 5th Feb 2008 is 2008-02-05
    1 becomes 01 or 001 or whatever the highest number in your range is#
    you can fake the same approach by using leading spaces if YOU MUST go down this route.

    the basic problem is that YOU are storing data in the wrong datatype. so either store it in the right datatype or be prepared to do a lot more formatting
    I'd rather be riding on the Tiger 800 or the Norton

  6. #6
    Join Date
    Jun 2007
    Location
    London
    Posts
    2,527
    change your order by clause to
    Code:
    order by tbl_transactions.startedby desc, 
                tbl_user.lastname
    or, if the field startedby is not a datetime, then you could use the following:
    Code:
    order by convert( tbl_transactions.startedby, date ) desc, 
                tbl_user.lastname
    but strictly speaking the date fields should be specified as date fields otherwise you'll be continually hitting problems like this. The other advantage of using the right type is that your data will be validated as a correct date before being stored.

    When you used order by 'date' desc you're just creating a string with the value 'date' for each row and then you're ordering by this string value. Because the string value is the same for each row ('date') then it won't make any difference to your order by clause. It certainly has nothing to do with the date stored in the database.

    It's also better to not use tbl_ for the start of all your tables unless you're prone to forgetting that your selecting your data from tables. Most people use an acronym for the name of the application etc. Of course, if you have lots of views and tables on the same database then there may be some justification for this.

    Mike

    PS just in case you need to ask questions again then it's probably best to provide the table definition as well as this allows people to see exactly what you're working with.

  7. #7
    Join Date
    Oct 2007
    Location
    Manila, Philippines
    Posts
    132
    Quote Originally Posted by r937
    would you kindly run this query please:
    Code:
    SHOW CREATE TABLE tbl_transactions
    Code:
    'tbl_transactions', 'CREATE TABLE `tbl_transactions` (
      `Transid` int(10) unsigned NOT NULL auto_increment,
      `ArtID` varchar(45) character set latin1 collate latin1_general_ci default NULL,
      `transcode` varchar(45) character set latin1 collate latin1_general_ci default NULL,
      `dtestarted` datetime default NULL,
      `StartedBy` varchar(10) character set latin1 collate latin1_general_ci default NULL,
      `dteCompleted` datetime default NULL,
      `CompletedBy` varchar(10) character set latin1 collate latin1_general_ci default NULL,
      `CustomerID` varchar(10) character set latin1 collate latin1_general_ci default NULL,
      `prevstatus` varchar(10) character set latin1 collate latin1_general_ci default NULL,
      `RecordID` int(11) default NULL,
      `errorflag` varchar(10) character set latin1 collate latin1_general_ci default NULL,
      PRIMARY KEY  (`Transid`),
      KEY `Index_2` (`ArtID`,`transcode`)
    ) ENGINE=MyISAM DEFAULT CHARSET=latin1'
    Take Nothing But Pictures;
    Leave Nothing But Footprints;
    Kill Nothing But Time;

  8. #8
    Join Date
    Apr 2002
    Location
    Toronto, Canada
    Posts
    20,002
    okay, try this --
    Code:
    SELECT FROM_DAYS(TO_DAYS(tbl_transactions.dtestarted)) AS date_started
         , tbl_transactions.startedby
         , tbl_user.lastname
         , tbl_errordetail.errcode
         , tbl_error.errdesc
         , COUNT(tbl_errordetail.errcode) AS 'error count'
      FROM tbl_user
    INNER
      JOIN tbl_transactions
        ON tbl_transactions.startedby = tbl_user.userid 
    INNER
      JOIN tbl_errordetail
        ON tbl_errordetail.transid = tbl_transactions.transid 
    INNER
      JOIN tbl_error
        ON tbl_error.errcode = tbl_errordetail.errcode 
     WHERE tbl_user.lastname = 'barrios'
    GROUP 
        BY date_started
         , tbl_transactions.startedby
         , tbl_user.lastname
         , tbl_errordetail.errcode
         , tbl_error.errdesc
    ORDER 
        BY date_started DESC
         , tbl_user.lastname
    Last edited by r937; 05-22-08 at 08:03.
    rudy.ca | @rudydotca
    Buy my SitePoint book: Simply SQL

  9. #9
    Join Date
    Nov 2004
    Location
    out on a limb
    Posts
    13,692
    Provided Answers: 59
    looking at the original query the problem I see there is formatting the date AND then using that formatted value for the sort order

    if you set the sort order to
    Code:
    tbl_transactions.dtestarted then it should be OK
    in place of
    ORDER BY date_started DESC
    , tbl_user.lastname

    incidentally Id suggest not using a reserved word such as DATE in your SQL. ...it may be OK in MySQL but it can cause problems elsewhere

    I'd also suggest that you do your date formatting in the presentation layer not the data extraction/manipulation layer
    I'd rather be riding on the Tiger 800 or the Norton

  10. #10
    Join Date
    Oct 2007
    Location
    Manila, Philippines
    Posts
    132
    thanks guys
    and all your suggestions were very helpful,
    i will not extract the date to manipulate it.
    and also my error in using the reserved DATE in concatenating the extracted date.

    problem solved....the best!
    Last edited by homer.favenir; 05-22-08 at 08:59.
    Take Nothing But Pictures;
    Leave Nothing But Footprints;
    Kill Nothing But Time;

  11. #11
    Join Date
    Apr 2002
    Location
    Toronto, Canada
    Posts
    20,002
    healdem, my interpretation of the problem is simply that he wanted to group by the date, and not the datetime

    tbl_transactions.dtestarted quite clearly is a datetime (it wouldn't make sense for transaction errors to be logged with just the date, ignoring the time)

    so your suggestion to replacve my date_started with the original column is wrong

    look back at his first attempt to deal with this -- he's trying to construct a date from the date portions of the datetime value

    grouping on datetime makes no sense, because you would get one error per group!!

    what he wants is to count the errors by day

    thus, he needs some way to strip the time off the datetime and group by the resulting date

    in SQL Server, the best technique is DATEADD(dd,DATEDIFF(dd,0,GETDATE()),0)

    in MySQL, the FROM_DAYS(TO_DAYS(...)) technique works best
    rudy.ca | @rudydotca
    Buy my SitePoint book: Simply SQL

  12. #12
    Join Date
    Nov 2004
    Location
    out on a limb
    Posts
    13,692
    Provided Answers: 59
    good point and well made
    I'd rather be riding on the Tiger 800 or the Norton

  13. #13
    Join Date
    May 2008
    Posts
    17
    Quote Originally Posted by r937
    in SQL Server, the best technique is DATEADD(dd,DATEDIFF(dd,0,GETDATE()),0)

    in MySQL, the FROM_DAYS(TO_DAYS(...)) technique works best
    Hi Rudy

    Why would
    FROM_DAYS(TO_DAYS(...))
    be used in MySQL instead of
    DATE(...)
    which appears to give the same result?
    Thanks

  14. #14
    Join Date
    Apr 2002
    Location
    Toronto, Canada
    Posts
    20,002
    good point and well made

    DATE() became available in MySQL 4.1.1

    in versions prior to that, i would go back to using FROM_DAYS and TO_DAYS
    rudy.ca | @rudydotca
    Buy my SitePoint book: Simply SQL

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •