Hello,
I have looked at various posts on the subject, but I still cant solve a ordering/sorting problem:
I have three fields in SQLLite table: vAuthor, vTitle, vVolume (in the list below, separated by a “/”)
Swift / Gulliver - Beginning / 1
Swift / Gulliver - Middle / 2
Swift / Gulliver - End / 3
Swift / Memories - Early / 1
Swift / Memories - Recall / 2
Swift / Memories - Last / 3
When I run a select with order by vAuthor,vTitle,vVolume
or order by vTitle,vVolume,vAuthor
I never get the above order, since the titles of volume 3 take alphabetical precedence over titles of volumes 2. Something like:
Swift / Gulliver - Beginning / 1
Swift / Gulliver - End / 3
Swift / Gulliver - Middle / 2
Swift / Memories - Early / 1
Swift / Memories - Last / 3
Swift / Memories - Recall / 2
Apart than hardcoding titles + volumes when creating a record (Gulliver 1 - Beginning, Gulliver 2 - Middle, etc), is there a syntax that could output the first list above?
Thanks.
Since you have a TITLE (Gulliver) and a SUBTITLE (BEGINNING/END/MIDDLE) as in one field, then ANY sort mechanism (including ORDER BY) will evaluate each on its own content…
So If possible I would create a new field (SubTitle) and then order by TITLE,Volume
There must be something the is unique to the series of books to group them together within the sort
What do you save in the database? The volume number or the volume name? You can sort by volume number. For volume name you need to do a custom sort function.
Volume number as a order by won’t work. .it would result in
Swift / Gulliver - Beginning / 1
Swift / Memories - Early / 1
Swift / Gulliver - Middle / 2
Swift / Memories - Recall / 2
Swift / Gulliver - End / 3
Swift / Memories - Last / 3
I think the OP wants
Swift / Gulliver - Beginning / 1
Swift / Gulliver - Middle / 2
Swift / Gulliver - End / 3
Swift / Memories - Early / 1
Swift / Memories - Recall / 2
Swift / Memories - Last / 3
and it can’t be done with the data as it is configured
@ Beatrix I save author, title and volume. But as Dave already pointed out, the output won’t work (1,1 2,2 3,3 etc.).
@Dave Yes, different fields for title and subtitle would solve the impasse. Unfortunately the app is a pretty old one, and users have already entered their values. That’s why I was hoping (without so much hope) for a possible solution.
sorry… I had an idea, then realized it would not work
you might look at available aggregate functions for what ever database engine you are using
you might be able to use INSTR, LEFT etc… to create a psudeo field for sorting
[quote=242191:@Merv Pate]What about a compound statement:
Select MyField1||MyField2||Myfield3 AS MyTempCol FROM MyTable ORDER BY MyTempCol
That introduces a sorting problem with the numbers as strings when they have a different number of decimals, but there may be ways around that.[/quote]
The existing data content needs to have parts of fields “ignored”… (ie. the last portion of the title) in order to create unique values that can result in the desire sort.
What you suggested is the same as
ORDER BY myfield1, myfield2, myfield3
not to mention that most database engines won’t allow you to use a derived field in the order by (if it is derived in the SELECT that is), as “myTempCol” doesn’t actually exist until the commit occurs.
I tried it here and it seemed to work in SQLite, which is what he is using. I only mentioned as something to explorer. If the volume could be returned used as 001 then it would properly sort 009 and 011 for example.
No matter how you concatnate them, you DO NOT get the desired sort order… period
and if you still think I’m wrong… please show me a solution based on what you have said that does provide the required result
SELECT Author, Title, Volume, SUBSTR(TITLE, 1, 1)||Volume AS MySortFlag FROM Books ORDER BY MySortFlag ASC
Of course that brings up what I have been trying to say, if you get into double digit volumes, then you introduce the problem of ‘11’ coming before ‘9’. That’s why I suggested returning a formatted number, i.e. 011 and 009 in the concatenated temporary field.
I know that it needs more characters than 1 from the title, I just don’t have time to work out the SQL string function, I have a deadline in 3 days. Just trying to give the guy another direction to look at.
a) that is NOT what you were describing before.
b) it is a incorrect variation of what I suggested (using appropriate Instr and LEFT)
c) you are sorting by the first letter of the title, when in fact you must sort by the complete UNIQUE part of the title as it relates to all the volumes in the series “TA - test / 1” and “TB - xyz / 1” group together in your example
the ONLY concat that MIGHT work is
ORDER BY left(title,instr(title,'-')) || volume
And even this ASSUMES that the Title is delimited by a “-” at the correct part in ALL records… an assumption that in real life I would be unwilling to make.
but this argument has become unproductive… there is only two solutions
using a derived key, assuming an appropriate one can be made across the entire database
re-engineer the data into TITLE and SUBTITLE fields
Assuming (big assumption) that each vitle has " - " separating the title and subtitle, and also that the title itself does not have an occurrence of " - ":
order by vauthor, substr(vtitle, 1, instr(vtitle, ' - ') -1), vvolume
and if some vtitles have a subtitle preceded by " - " while others do not:
order by vauthor,
case when instr(vtitle, ' - ') = 0 then vtitle
else substr(vtitle, 1, instr(vtitle, ' - ') -1) end,
vvolume
A title containing " - " would still be a problem.
that the “subtitle” is in the title is the basic problem
suppose you had
Swift / Gulliver Beginning / 1
Swift / Gulliver Middle / 2
Swift / Gulliver End / 3
Swift / Memories Early / 1
Swift / Memories Recall / 2
Swift / Memories Last / 3
Hence the big assumption comment. I’m trying to give him some ideas that may work depending on his situation, rather than just making a blanket statement that nothing can be done.
I appreciate all the suggestions, and even more the willingness to find a way-out for my particular problem.
I was tempted by
order by vauthor, substr(vtitle, 1, instr(vtitle, ’ - ') -1), vvolume and its variations, but then I said to myself:
if I have to tell old users to check their titles and in case replace all separators into " - ",
if in a particular language " - " does not make sense as a separator (this particular point was not contemplate in my original post)
then it is better to add a subtitle field, and old users, if they think it agreable, will cut the subtitle from the title and past it into the new field.
Again, my appreciation for the constructive willingness to help me out.
BTW For the listbox sorting I used to put the first portion of the title into an invisible column, and call it when the user clicked the title column. Beside “-” I checked also for possible separators such as “:” and “,”. It somehow worked, but considering the different ways users separate title from subtitle, it was still an unreliable patch.
Better off with a proper and clean solution.