Sqlite order by string of numbers9/12/2023 ![]() Is there a COLLATE clause value that would sort Case Insensitive? I have pored over the documentation and searched high and low, but I can’t find anything so straightforward for PostgreSQL. MSSQL: Latin1_General_BIN | Latin1_General_CI_AS MariaDB: utf8mb4_bin | utf8mb4_general_ci In other DBMSs (tested in SQLite, Oracle, MariaDB, MSSQL) I can override the default sort order using the COLLATE clause: SELECT * I appreciate any assistance someone can give me! I can share my query if that is helpful.I have spent a long time looking for this, and I am getting mixed messages. I will need to add some delimiters in the output but I think I can figure that out. MRN NAME SVC RSRC RSRC RSRC RSRC PROV PROV PROV PROV PROV PROV TIMEġ23456 Smith Mary MRI 1 L MRRMS 60 1 RADS 60 2 NURSE 60 MRN Name Svc Rsrc type RSRC ID RSRC TIME Provider PROVIDER TIMEġ23456 Smith, Mary MRI L MRRMS 60 RADS 60 I get this (abbreviated output) When I have one location and two providers, or two locations and one provider, I get six lines per appointment. Since the providers are stored in one table, and the location stored in another table, I get multiple lines for each appointment. Many of my appointments use a provider (person) and a location (room/equipment). I have patient demographic and appointment/service information that would only qualify once, but then I get into details as to the resource or provider attached to that appointment. I am writing a query to extract various fields from about 10 linked records and need to create a delimited CSV file, formatted one line per record appointment, even though I have multiple records due to all the files I am linking. I am not a developer and fairly new to SQL queries. However for non-production code it’s a very simple solution. As such it comes with a “health warning” as it maybe that Microsoft will remove the ability to do this in the future without warning. This behaviour is certainly unexpected, and also appears to be undocumented. I adapted this solution from some code I found on a newsgroup a while ago, and was astonished that it worked. SQL iterates around each row in the table to produce the result. The SQL below simply creates a variable then concatenates the value of the row. This solution is perhaps the most surprising, in that at first glance you might not expect it to work. ![]() This solution is perhaps the easiest to understand and the most flexible, where performance isn’t an issue. However if the number of rows is small, as it is here, then this can be a useful approach. Generally looping constructs in SQL are to be avoided as they can perform badly. SET =( SELECT Txt FROM ConcatenationDemo WHERE SET = ( SELECT MAX (RowID ) FROM ConcatenationDemo ) The following code uses a WHILE loop (this could be rewritten to use a cursor) : We can do the same thing in SQL Server using either a while loop or cursor. In the procedural world the chosen solution would probably be some sort of looping construct. Solution 3 : Using a WHILE loop or Cursor This is a significant limitation of this approach. One thing that you may have noticed is that the code is specific for the number of rows in the table – the code would have to be modified if there was an extra row. Note that in the this example no aggregation is necessary so this could equally well be a MIN(). Normally we would have some type of aggregation as the rows are pivoted to columns, hence the use of the MAX() here. ![]() I’ve used the PIVOT command to convert the rows to an equivalent number of columns and then concatenated the result together. PIVOT ( MAX (Txt ) FOR RowID IN (, ,, ,, ,, )
0 Comments
Leave a Reply.AuthorWrite something about yourself. No need to be fancy, just an overview. ArchivesCategories |