Results 1 to 6 of 6
  1. #1
    Join Date
    Jul 2010
    Location
    Norway
    Posts
    45

    Unanswered: SQL - Sorting a column descending order which contains two years

    Hello


    I am trying to sort this simplied table:

    ID - Time
    1 2000-2001
    2 2002-
    3 2001-2003
    4 1999
    5 2005-2006


    I want this as a result:
    1999
    2000-2001
    2001-2003
    2005-2006
    2002-


    Because the "-" means "continues", it the thing is still activated, so if it makes it easier, i could put the today's year afterwards during the query, if it ends with a - ...


    Now, simply doing a
    SELECT * FROM [table] ORDER BY TIME;

    Sorts it "perfectly", apart from the "2002-" is just placed before 2005 and after 2001.

    So, of course, it fails on all entries with a leading "-" ...

    Suggestions?

    EDIT; right when I clicked "submit", of course, I can simply replace all entries with a time ending with "-", with the todays Year, so at least they will get at the end of the query...
    Well, have to do a union, first sorting all without the "-", then sorting all with the "-", and that should be it... Ok, cool, thanks!
    Last edited by ManyTimes; 05-22-14 at 05:29.

  2. #2
    Join Date
    Feb 2008
    Location
    Japan
    Posts
    3,483
    Please use rational column names.
    Your "time" column contains a year or year range (not time like '12:34:56.789'),
    so it might be better to name the column with other name like Year_Range.


    Anyhow,
    how about this?

    Code:
    SELECT @t.*
     FROM  @t
     ORDER BY
           CASE RIGHT(Year_Range , 1)
           WHEN '-' THEN
                '9999'
           ELSE RIGHT( REPLACE(Year_Range , '-' , '') , 4 )
           END
    ;

  3. #3
    Join Date
    Jan 2013
    Posts
    354
    Provided Answers: 1
    We want to see DDL; this is minimal Netiquette. TIME is a reserved word in SQL and it does not mean year intervals. NULL means still open. Where is the regular expression for your invented notation?

    I would use two temporal columns

    CREATE TABLE Foobar
    (start_year CHAR(4) NOT NULL
    CHECK (start_year LIKE '[12][0-9][0-9][0-9]'),
    end_year CHAR(4)
    CHECK (end_year LIKE '[12][0-9][0-9][0-9]'),
    CHECK (start_year < end_year),
    UNIQUE (start_year, end_year));

    Look at UNIQUE and not PRIMARY KEY in this. We have NULLs, so we have to do use this.

  4. #4
    Join Date
    Jul 2010
    Location
    Norway
    Posts
    45
    Well... For your ranting about the name of the column, "time"... Which I hopefully said was "simplied" (typo, should've been simplified ).

    Thanks a ton tonkuma, lovely and spot on.

    Result:
    Code:
    SELECT * FROM  employeeFields
    WHERE employeeFields.type = 3
    AND employeeID = 253150610613
    ORDER BY 
    	CASE RIGHT(timeperiod, 1)
        WHEN '-' THEN
            '9999'
        ELSE RIGHT( REPLACE(timeperiod , '-' , '') , 4 )
        END
    ;
    Lovely, casing of the right last letter, if it is - then 9999, else replacing and then selecting the right 4 for the ordering... Quite nifty and short compared to my solution with union two seperate queries.

  5. #5
    Join Date
    Feb 2008
    Location
    Japan
    Posts
    3,483
    I reviewed my query.
    Then I thought that REPLACE might be unnecessary.
    "ELSE RIGHT(timeperiod , 4)" must be sufficient.

  6. #6
    Join Date
    Jul 2010
    Location
    Norway
    Posts
    45
    That is true, theres no need to replace the - with '', to then right-select the 4 char. I might remove it, or not...I've moved on, kind of, thanks though!

Posting Permissions

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