On 2/8/08, jose isaias cabrera <[EMAIL PROTECTED]> wrote: > > "P Kishor" replied... > > > > On 2/7/08, jose isaias cabrera <[EMAIL PROTECTED]> wrote: > >> > >> "P Kishor" asked... > >> > >> > >> >I don't understand your question at all. > >> > >> I will answer it at the end. > >> > >> > On 2/7/08, jose isaias cabrera <[EMAIL PROTECTED]> wrote: > >> >> > >> >> Greetings. > >> >> > >> >> I would like to have the results of a select be returned sorted in an > >> >> specific way. Let me show you what I mean: > >> >> > >> >> sqlite> SELECT PSubClass FROM LSOpenJobs WHERE subProjID = 2190 GROUP > >> >> BY > >> > > >> > But, first... why are you using GROUP BY above when you have no > >> > aggregate function (Count, Max, Min, Sum, etc.) in your query? > >> > >> Because of this: > >> > >> sqlite> SELECT PSubClass FROM LSOpenJobs WHERE subProjID = 2190; > >> Pre-Proc > >> Post-Proc > >> DOC-Trans > >> DTP > >> Pre-Proc > >> Pre-Proc > >> Pre-Proc > >> Pre-Proc > >> Pre-Proc > >> Post-Proc > >> Post-Proc > >> Post-Proc > >> Post-Proc > >> Post-Proc > >> DOC-Trans > >> DOC-Trans > >> DOC-Trans > >> DOC-Trans > >> DOC-Trans > >> DTP > >> DTP > >> DTP > >> DTP > >> DTP > >> PM > >> PM > >> PM > >> PM > >> PM > >> PM > >> sqlite> > > > > In that case you need SELECT DISTINCT, not GROUP BY > > Oh, ok, thanks for the help. Weird how the ORDER BY works... I know what > each of these (ORDER BY and DISTINT) do, however is the usage of one of > these faster than the other? > > > > > > >> > >> > >> > > >> >> PSubClass; > >> >> DOC-Trans > >> >> DTP > >> >> PM > >> >> Post-Proc > >> >> Pre-Proc > >> >> sqlite> > >> >> > >> >> What I would like is to have the SELECT result be, > >> >> > >> >> Pre-Proc > >> >> Post-Proc > >> >> DOC-Trans > >> >> DTP > >> >> PM > >> >> > >> >> is this possible? Yes, I know I can sort it in the program, but how > >> >> can > >> >> I > >> >> get this special sort from the DB? > >> >> > >> > > >> > What is the logic above? Without you telling us why Pre-Doc is before > >> > Post-Proc is before DOC-Trans, and so on, how can we guess what your > >> > logic is for the sort? In other words, please explain what makes the > >> > above sort "special"? > >> > >> Aaah, ok. I am preparing an html file and in it I will list these > >> PSubClasses in this special logical display sequence: > >> 1. Pre-Proc > >> 2. Post-Proc > >> 3. DOC-Trans > >> 4. DTP > >> 5. PM > >> > >> I hope this explains it... > >> > > > > Nope, it doesn't at all. You logic is not clear to me, and even if it > > is, it won't be clear to the poor program that you want to write. If > > you want a specific order which is not dependent on a collation > > sequence, alpha order, etc., then enter your prescribed ordinal-ity in > > a separate column and ORDER BY that column. > > Ok Punkish, no problem. Thanks for trying. You have helped me many times > and I am thankful. Sometimes I am very hard to understand. However, other > folks in the list were able to understand what I meant and they have > provided a few solutions for me. Thanks for the help. And the poor program > understands me correctly and it is working as desired .-). It is a good > thing computers understand me. Which I can not say the same for people... > .-)
Hi José, I apologize if my email came off as brusque and rude sounding. That was not the intent at all. I really mean what I say above, and what I suggest above is exactly what has been suggested by others you have replied to you as well. The logic that may be very clear in our brain has to be explicit for the program. Using ORDER BY depends on alphabetical sort or a numeric sort. If you want something else (for example, importance of a job title), you have to convert that hierarchy into something that the ORDER BY mechanism can understand. But, you have to do that explicitly. Remember, there is no inherent concept of order in an rdbms. Each row is independent of each other. Order is imposed *after* your result set has been extracted, and it only changes the presentation of the result set, not the original data. There is no inherent concept of "previous row" or "next row" unlike a spreadsheet. Puneet. _______________________________________________ sqlite-users mailing list sqlite-users@sqlite.org http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
