Complex sort for SQL Query?
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
Software/Hardware used:
Oracle DB
ASKED:
October 9, 2012 8:40 PM
Answer Wiki:
Last Wiki Answer Submitted:
Be the first to answer this question.
To see all answers submitted to the Answer Wiki: View Answer History.
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
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.
“…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.
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”;
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”;
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
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.
Oh, I just realised that maybe I’m confusing terms. That one is not the oldest, but the newest date.
I’m thinking maybe the OP confused terms. Hard to tell without a response. — Tom