Ordering Results

[infra]

Hi guys,First time poster.I'm a little,... well very rusty, with my SQL. Figured you experts out there would have an idea as to how to solve my problem here.Little background on the database:-It's a historical database with events and people from time; each record has a name, description, a start year, an end year, startYear suffix*, endYear suffix**Suffix = BC or AD or MYACorresponding with the above information, here is an example record:Example#1:John Doe | Famous Musician born in the 19th century | 1839 | 1910 | AD | ADExample2:Roman Period | The Roman Empire | 63 | 476 | BC | ADExample 3:Stone Age | The Human Era | 5 | 2500 | MYA | BCThe problem:Now, this is simple enough, but I have BC and MYA dates as well. So there could technically be an 1839 AD as well as an 1839 BC date or even an 1839 MYA year.Figuring out which record to return (the AD, BC, or MYA) is not a problem by itself, but when I have to return a set of records say from the year 2000MYA to 2000AD, you can see that things might not be so clean cut if I simply order the records via ORDER BY ASC/DESC in relation to the starting year of the record.I'm hoping there is a way to do "two" or "three" selects and combine the results where if I searched from 2000MYA to 2000AD, the first part would return all records which years are from a MYA era, in this case 2000MYA, ordered in an ascending fashion all the way to 0.Then, combine the results with another search that selects all records greater than the year 0 (BC dates in this case) ordered in an descending fashion then finally list the AD dates in an ascending manner.You'd get records that show years like such:2000 MYA, 1500 MYA, 500 MYA, 30 MYA, 5 BC, 350 BC, 2000 BC, 1500 BC, 10 BC, 15AD, 1500 AD, 1993 AD, 2009 ADSo in SQL terms, the statement would look like follows (of course this statement does NOT work, but you get the idea of what I'm trying to do):select * from tablename WHERE startYearSuffix = 'MYA' ORDER BY event_Start DESCUNIONselect * from tablename WHERE startYearSuffix = 'BC' ORDER BY event_Start DESCUNIONselect * from tablename WHERE startYearSuffix = 'AD' ORDER BY event_Start ASC;Thanks a lot for the help.

[justsomeguy]

Something like that could work, you could use UNION DISTINCT to make sure it doesn't return duplicates. You'll need a bunch of other WHERE conditions to filter more than just the suffix.I'm not sure what happens if you union several sorted result sets. I'm not sure if it concatenates the sorted results onto the end of the previous set, or what. If it does, then something like that statement would work. If it needs to sort the entire result set by the same criteria, then I don't think there's a way to do it in SQL, I think you need to do that manually with an algorithm that's going to check the suffix before the date. The problem is the direction of the date column changes depending on the suffix.Another way to do it might be to just store the MYA and BC dates as negative numbers, so instead of 2000 MYA in the database you would store -2000 MYA. You would need to add some logic to make sure to add the negative sign to those searches, and when you write the dates out you can just use the absolute value to eliminate the negative. If the before-AD dates were negative, at least you could order the entire result set the same direction.