Tag Info

New answers tagged

0

If you simply include the column which holds these values into an order by clause they will come out in the sequence you want. For example select ThisColumn from MyTable order by ThisColumn This will work if your values genuinely are all form shown i.e. two digits, a slash and one digit. If you have other formats the more involved solutions will be ...


1

A way to order such string is Step1: Create Split Function CREATE FUNCTION [dbo].[fnSplit] ( @List VARCHAR(8000), @Delimiter CHAR(1) = ',' ) RETURNS @Temp1 TABLE ( ItemId INT IDENTITY(1, 1) NOT NULL PRIMARY KEY, Item VARCHAR(8000) NULL ) AS BEGIN DECLARE @item VARCHAR(4000), @iPos INT SET @Delimiter = ISNULL(@Delimiter, ',') SET @List = ...


0

First you find the position of your separating character ( / ). Then you order your table by left side of separator and right side of separator. SELECT field ,CHARINDEX('/', field) as [Position of /] ,LEFT(field, CHARINDEX('/', field)-1) as Left_Sort ,RIGHT(field, LEN(field) - CHARINDEX('/', field)) as Right_Sort from #string order by ...


1

You didn't tell us what the "failed attempt" means. But something like this should work: with ldist as ( select name, levenshtein(substring(name,1,200), lag(name) OVER (order by name)) as distance FROM books WHERE name <> '' ) select * from ldist order by distance; you don't want partition by name because that essentially ...


1

Try adding books.id to ORDER BY. If books_data.date is not unique, relative order of rows in resultest is not guaranteed. Say you have 5 rows with date1, 10 rows with date2, and 5 with date3; date1 < date2 < date3. Then some rows with date2 may appear on both pages, some on neither, and , if you are lucky enough, you may sometimes see expected ...


2

It can make sense to insert data in order. There's lots of caveats to this though. If the data isn't frequently updated and if you're using certain types of tables or indexes (e.g. IOT, clustered indexes). Being in order means that if you're doing a range scan of the ordered column (e.g. BETWEEN x AND Y) then the data is more likely to be in a ...


0

I've solved it by doing the following: SELECT nWidth, nHeight, nColumn, (SELECT MAX(t.nRow + (t.nHeigth - 1)) FROM test t WHERE t.nRow = tp.nRow GROUP BY t.nRow LIMIT 1) AS nRow FROM test tp ORDER BY nRow, tp.nColumn This will get the max from the row number plus it's heigth minus 1. So, if we have a row that has a cell with height equal or higher ...


3

Although the syntax for CREATE INDEX supports an option for defining the index as ascending or descending, in the most common two storage engines (InnoDB and MyISAM), this index option is a no-op. There is no such thing as an "ascending" or "descending" index, they are just indexes and can be used for sorting in either direction. But in the case you ...


2

One way: SELECT id FROM products WHERE details->'make' = ? OR details->'model' = ? OR details->'year' = ? ORDER BY CASE WHEN details->'make' = ? THEN 0 ELSE 1 END + CASE WHEN details->'model' = ? THEN 0 ELSE 1 END + CASE WHEN details->'year' = ? THEN 0 ELSE 1 END; It's a bit simpler to invert the logic: ...


0

Can you please post an example of your query? Without knowing what your query structure look likes, this will be difficult to fix. However here is an example that may point you in the right direction based off of your post title, in regards to ORDER and GROUP: SELECT pkey, COUNT(int), FROM my_table LEFT JOIN this_table USING (key) GROUP BY 1 ...



Top 50 recent answers are included