So I have a bunch of varchars that I need to sort in a certain way, first be alpha, and then treat the following number as a whole number (i.e. A2 would be before A10). That's the main problem, and then I have some with 'sub' letters after like A1M. Don't want to have to add a new column just for sorting, so I wanted to see if a more complex ORDER BY statement might be able to handle something like this.  They should be sorted like this:  A1 A1M A2 A6 A6B A9 A10 A15 A16 AS10   But it currently sorts like this (standard ORDER BY):  A1 A10 A15 A16 A1M A2 A6 A6B A9 AS10
16967
Is there an elegant way to have performant, natural sorting in a MySQL database?  For example if I have this data set:   Final Fantasy Final Fantasy 4 Final Fantasy 10 Final Fantasy 12 Final Fantasy 12: Chains of Promathia Final Fantasy Adventure Final Fantasy Origins Final Fantasy Tactics   Any other elegant solution than to split up the games' names into their components    Title: "Final Fantasy" Number: "12" Subtitle: "Chains of Promathia"   to make sure that they come out in the right order? (10 after 4, not before 2).  Doing so is a pain in the a** because every now and then there's another game that breaks that mechanism of parsing the game title (e.g. "Warhammer 40,000", "James Bond 007")
59026
Is there an AmpScript function that can be used in an equivalent manner to sql left, right and mid functions?   Thanks, Mark
28318
I'm trying to format numbers. Examples:  1     =&gt; 1 12    =&gt; 12 123   =&gt; 123 1234  =&gt; 1,234 12345 =&gt; 12,345   It strikes as a fairly common thing to do but I can't figure out which filter I'm supposed to use.  Edit: If you've a generic Python way to do this, I'm happy adding a formatted field in my model.
79673
e.g,  foo1 foo2 foo10 foo100   rather than  foo1 foo10 foo100 foo2   Update: not interested in coding the sort myself (although that's interesting in its own right), but having the database to do the sort for me.
75128
Is there an elegant way to do this:  SELECT Cols from MyTable WHERE  zip = 90210 OR zip = 23310 OR zip = 74245 OR zip = 77427 OR zip = 18817 OR zip = 94566 OR zip = 34533 OR zip = 96322 OR zip = 34566 OR zip = 52214 OR zip = 73455 OR zip = 52675 OR zip = 54724 OR zip = 98566 OR zip = 92344 OR zip = 90432 OR zip = 91532 OR ...   (zip codes in this post are ficticious and bear no resemblance to actual zip codes living or dead)
107704
I have data like multiples of 40. 40 80 120 160 200 and so on.    I would like to label every record that is a multiple of 100.  I have tried a couple of methods and can't get them to work either as a query in the label or as a select statement.  "STATION" =  %00   RIGHT ("STATION", 2) =  00     I get invalid SQL statement. Does anyone see what I am doing wrong?    Respectfully I did search and did not see any of the example questions you presented.  However it still does not answer my question, re-stated, what am I doing wrong?
60098
I want to format an SQL table the right way in a Stack&nbsp;Overflow question.  ID Name  1 Tom  I know the example above is not the right way. I even tried an HTML table approach, but it's still wrong. Please help, because I need to include SQL tables later...
94993
I'm having an issue sorting data in ascending order.   Right now the sort order is: 1.0.0  10.0.0 10.0.1 11.0.0 2.0.0 2.0.1 3.0.0 3.0.1 3.1.0  What I want is: 1.0.0 2.0.0 2.0.1 3.0.0 3.0.1 3.1.0 10.0.0 10.0.1 11.0.0  How can I achieve this? The datatype can't be number or currency because of the 2 decimals, so I used text-field.
42529
I have a table of choir members with a “name” and a “voice type” column. How can I write a query that orders them by decreasing pitch level of the voice type (i.e. soprano > contralto > tenor > bass)?  Is there in the  something similar to ?  I also tried the , but it does not seem to have an option for custom ordering, either.
38547
In an enumerate environment, I want a few (not all) of the items to have asterisks which appear before the number (I don't feel strongly about whether the number or the asterisk is horizontally aligned with the other numbers).  Is there a less crufty way to do this than, say, using multiple lists (or &mdash; horrors &mdash; numbering manually)?  I'm already using enumitem for an unrelated purpose, if that helps, although I didn't find a solution after a quick skim of its manual.
14196
I realize that CHAR is recommended if all my values are fixed-width. But, so what? Why not just pick VARCHAR for all text fields just to be safe.
86778
How does SQL Server figure out the order of the records in the result set of query execution?  I am trying to make heads or tails of it but find myself scratching my head. When I change the fields I am selecting the order also changes. When I execute the below SQL with a SELECT * I get the same records but in a much different order.   SELECT TOP (900)     AD.ATTACHMENTID,     AD.NAME,     AD.ISINLINE,     AD.INSERTEDDATETIME,     ATMT.ATTACHMENTBLOB,     U.UFID   FROM ATTACHMENTDETAIL AD WITH (NOLOCK)   INNER JOIN MESSAGEATTACHMENT MA ON MA.ATTACHMENTID = AD.ATTACHMENTID   INNER JOIN ATTACHMENT ATMT ON ATMT.ATTACHMENTID = AD.ATTACHMENTID   INNER JOIN MESSAGE MSG ON MSG.ID = MA.MESSAGEID   INNER JOIN MESSAGEDETAIL MD ON MD.MESSAGEID = MA.MESSAGEID   INNER JOIN [USER] U ON U.ID = MD.USERID   LEFT OUTER JOIN XmlExtractionMapping XM ON MA.MESSAGEID = XM.MessageId     WHERE AD.FILEBOXTOKEN IS NULL      AND (XM.XMLEXTRACTIONDATE IS NOT NULL OR           (MSG.MESSAGESOURCEID = 1 AND MD.FolderId &lt;&gt; -4))     AND AD.ISINLINE = 'FALSE'
98617
I have a table of data built from a file inventory. I then want to add a categorical column so that I may easily perform GROUP BY.  How things start:  SELECT COUNT(fullpath) FROM data;  12395   I use the column fullpath a lot so I create an index:  CREATE INDEX "index" ON data (fullpath);   I then add the column:  ALTER TABLE data ADD COLUMN 'Sub' VARCHAR(20);   Insert values into the Sub column based on the fullpath:  insert into data(Sub) select case     when fullpath like '%subA%' then 'subA'     when fullpath like '%subB%' then 'subB'     else 'neither' end from data;   Check the number of rows again:  SELECT COUNT(fullpath) FROM data;  24790   I don't understand the syntax and the operations. Do I need to join or something to respect the index?
62314
I have a web-based application that notifies users of activity on the site via email. Users can choose which kinds of notifcations they want to receive. So far there are about 10 different options (each one is a true/false).  I'm currently storing this in one varchar field as a 0 or 1 separated by commas. For example: 1,0,0,0,1,1,1,1,0,0  This works but it's difficult to add new notification flags and keep track of which flag belongs to which notification. Is there an accepted standard for doing this? I was thinking of adding another table with a column for each notification type. Then I can add new columns if I need, but I'm not sure how efficient this is.  Thanks in advance!
799
While trying to write a query, I found out (the hard way) that SQL Server parses WHEREs in a query long before parsing the SELECTs when executing a query.  The  say that the general logical parsing order is such that SELECT is parsed nearly last (thus resulting in "no such object [alias]" errors when trying to use a column alias in other clauses). There was even  to allow for aliases to be used anywhere, which was shot down by the Microsoft team, citing ANSI standards compliance issues (which suggests that this behavior is part of the ANSI standard).  As a programmer (not a DBA), I found this behavior somewhat confusing, since it seems to me that it largely defeats the purpose of having column aliases (or, at the very least, column aliases could be made significantly more powerful if they were parsed earlier in the query execution), since the only place you can actually use the aliases is in ORDER BY. As a programmer, it seems like it's missing a huge opportunity for making queries more powerful, convenient, and DRY.  It looks like it's such a glaring issue that it stands to reason, then, that there are other reasons for deciding that column aliases shouldn't be allowed in anything other than SELECT and ORDER BY, but what are those reasons?
67853
Is it possible to do a SELECT statement with a predetermined order, ie. selecting IDs 7,2,5,9 and 8 and returning them in that order, based on nothing more than the ID field?  Both these statements return them in the same order:   SELECT id FROM table WHERE id in (7,2,5,9,8)    SELECT id FROM table WHERE id in (8,2,5,9,7)
98205
I am typesetting an ordered list where the entries may appear out of order, but should be assumed in order otherwise. I don't want to have to jump through \setcounter hoops to do this. That is, I want something like  \begin{enumerate}   \item First   \item Second   \item[8] Eighth!   \item Ninth!   \item[4] Fourth \end{enumerate}   to give the following Good output, instead of the Bad output:    A minimum working example follows.  \documentclass{article} \usepackage{enumitem} % preferably something compatible with this guy \begin{document}  Good:  \begin{enumerate}   \item[1.] First   \item[2.] Second   \item[8.] Eighth!   \item[9.] Ninth!   \item[4.] Fourth \end{enumerate}  Bad:  \begin{enumerate}   \item First   \item Second   \item[8] Eighth!   \item Ninth!   \item[4] Fourth \end{enumerate}  \end{document}   It should be noted that I don't want to have to specify the period afterwards, in case my list using a different numbering, e.g. Roman. Compatibility with enumitem is preferred, but not required.  What is the correct way of going about this?