Complex sort for SQL Query?

50 pts.
Tags:
Oracle
SQL
I have a table: MY_TABLE IDENTIFIER (VARCHAR) NAME (VARCHAR2) DATE (DATE) SELECT NAME, IDENTIFIER, DATE FROM MY_TABLE ORDER BY IDENTIFIER, DATE; So that works great for ordering the identifiers (basically making them look like a grouping in the output). Then sorting them by date (makes it sort within each unique identifiers results). However, the results are of course ordered by the identifiers. So I'll start w/ an example of what I have and what I actually want. Example (of how sort works): Name, identifier, DATE Name1, 1, 5/1/2012 Name2, 1, 6/1/2012 Name3, 2, 1/3/2012 Name4, 2, 9/3/2012 Name5, 3, 1/2/2012 Example (of how I want it to work): Name, identifier, DATE Name3, 2, 1/3/2012 Name4, 2, 9/3/2012 Name5, 3, 1/2/2012 Name1, 1, 5/1/2012 Name2, 1, 6/1/2012 So basically I want to group the identifiers and sort them by date within the group. But then I also want to sort the grouping by DATE of the older DATE value in the group (rather than having them sorted by the identifiers value). So been trying to figure out how do this via a single executed select statement and cannot figure it out. Is this possible? If not I figure I could query on unique identifiers and sort those by date. Then for each of those do an SQL query to return on those results and sort then. Then combine and display all the results toward the end. I would like to know if this is possible and what my pro/cons for alternatives are?

Software/Hardware used:
Oracle DB

Answer Wiki

Thanks. We'll let you know when a new response is added.
Send me notifications when members answer or reply to this question.

REGISTER or login:

Forgot Password?
By submitting you agree to receive email from TechTarget and its partners. If you reside outside of the United States, you consent to having your personal data transferred to and processed in the United States. Privacy

Discuss This Question: 9  Replies

 
There was an error processing your information. Please try again later.
Thanks. We'll let you know when a new response is added.
Send me notifications when members answer or reply to this question.

REGISTER or login:

Forgot Password?
By submitting you agree to receive email from TechTarget and its partners. If you reside outside of the United States, you consent to having your personal data transferred to and processed in the United States. Privacy
  • TomLiotta
    What does the "identifier" identify? It doesn't seem to identify a name nor a date. It would seem to be an "identifier" for some entity that is defined in a different table.   Your "example" of how you want it to work doesn't seem to have any identifiable sequence. It does seem to be 'grouped by' the "identifier" but the "identifier" has no meaningful ordering. Within each group, dates are ordered; but the 'oldest' date isn't in the first group -- it's in the second group.   More info is needed about how you want this to work. Perhaps an entirely new set of example values might even be helpful.   Tom
    125,585 pointsBadges:
    report
  • ToddN2000
    If I understand how you are looking to sort this, I don't think you can do it with one table. It sounds like you want them sorted by date to find the order for your groups then sort all related group detail data by date within that group. You might try and create an alias table that is a summary of groups in date order. Then use this table in group/date order to get the full detail in date sequence.
    11,190 pointsBadges:
    report
  • carlosdl
    "...dates are ordered; but the ‘oldest’ date isn’t in the first group — it’s in the second group"  Actually, the oldest date is in the first group, which happens to be for the identifier '2'.  If I understand correctly, something like this should do the trick: SELECT name,identifier,"DATE",(select MAX("DATE") FROM your_table WHERE identifier = yt.identifier) from your_table yt ORDER BY 4 desc,2,3;   I think something like this is what Todd was suggesting too. 
    69,510 pointsBadges:
    report
  • carlosdl
    On recent Oracle versions, this should also work: WITH t AS ( SELECT identifier,max("DATE") max_date FROM your_table GROUP BY identifier ) SELECT yt.name,yt.identifier,yt."DATE" FROM your_table yt JOIN t on yt.identifier=t.identifier ORDER BY t.max_date DESC,yt.identifier,yt."DATE";
    69,510 pointsBadges:
    report
  • carlosdl
    And the simplest one, using an analytic MAX: SELECT name,identifier,"DATE",MAX("DATE") OVER (PARTITION BY identifier) md FROM your_table ORDER BY md DESC,identifier,"DATE";  ;-)
    69,510 pointsBadges:
    report
  • TomLiotta
    Actually, the oldest date is in the first group, which happens to be for the identifier ’2′.   What am I missing? The oldest date I can see is "1/2/2012", for identifier '3', which should be either 1 Feb 2012 or Jan 2 2012. The oldest date for identifier '2' is "1/3/2012". Whichever date format is used, "1/2/2012" is  an earlier (oldest) date. Isn't it? (Maybe I'm too tired lately.)   Tom
    125,585 pointsBadges:
    report
  • carlosdl
    This would be the record with the oldest date (Name4, 2, 9/3/2012), and I think the format being used is d/m/yyyy.  The text formatting in the question doesn't help much.  
    69,510 pointsBadges:
    report
  • carlosdl
    Oh, I just realised that maybe I'm confusing terms. That one is not the oldest, but the newest date.
    69,510 pointsBadges:
    report
  • TomLiotta
    I'm thinking maybe the OP confused terms. Hard to tell without a response. -- Tom
    125,585 pointsBadges:
    report

Forgot Password

No problem! Submit your e-mail address below. We'll send you an e-mail containing your password.

Your password has been sent to:

To follow this tag...

There was an error processing your information. Please try again later.

REGISTER or login:

Forgot Password?
By submitting you agree to receive email from TechTarget and its partners. If you reside outside of the United States, you consent to having your personal data transferred to and processed in the United States. Privacy

Thanks! We'll email you when relevant content is added and updated.

Following