Tagged Questions
1
vote
2answers
29 views
Order by on an alphanumerical column
I have a column that has strings of the following type:
Miller 10
Allen 20
King 10
....
Jones 100
I try to sort the column based on the numerical part of the data.
I tried the following:
SELECT ...
3
votes
1answer
50 views
Special ordering
I have a hierarchical query but I don't manage to order it as I want.
I have a column named sequence that indicates how to order it but the data is kinda mixed up and I cant do it properly.
This a ...
3
votes
1answer
73 views
Bayesian ranking
I have the following table in my database:
╔════════╦═════════════╦═══════════════╗
║ Name ║ total_stars ║ total_reviews ║
╠════════╬═════════════╬═══════════════╣
║ Item A ║ 27 ║ ...
5
votes
1answer
146 views
How does SQL Server order results when joins are used?
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 ...
1
vote
2answers
77 views
Special Oracle Order
I have a table (emp) with two columns in ORACLE:
COMPANY OFFICE EMP
------------------------
9999 00001 emp1
9999 00001 emp2
9999 00002 emp3
9999 00002 emp4
...
4
votes
2answers
380 views
Is SELECT ROW_NUMBER() guaranteed to return results sorted by the generated row numbers?
E.g. consider the SQL query:
SELECT
A.[Name],
ROW_NUMBER() OVER(ORDER BY A.[Name] ASC)
FROM
[FooTable] AS A
Here I observe the results being returned sorted by A.[Name]. If I change the ...
0
votes
0answers
99 views
GROUP_CONCAT with ORDER BY , but results are not orderd
SELECT
*,
(SELECT
GROUP_CONCAT(url SEPARATOR '$$' )
FROM project_photos
WHERE project_id = projects.id
ORDER BY priority) AS images
FROM projects
WHERE catID = 2
LIMIT 0,5
above query works fine but ...
18
votes
4answers
1k views
Why does ORDER BY not belong in a View?
I understand that you cannot have ORDER BY in a view. (At least in SQL Server 2012 I am working with)
I also understand that the "correct" way of sorting a view is by putting an ORDER BY around the ...
3
votes
1answer
308 views
How to order query results by tag weight in IN('tag1', 'tag2', 'tag3')
Consider a simple query as
SELECT * FROM posts
JOIN tags USING(post_id)
JOIN tag_map USING(tag_id)
WHERE tags.tag IN('tag1', 'tag2', 'tag3')
tag_map is a many-to-many relationship between tables ...
3
votes
2answers
1k views
MySql: Delete all rows greater than n entries ordered by datetime
Can someone help me with a MySql query to delete all rows greater than n entries ordered by date?
I.e. say I have 1200 rows of data with a timestamp column. I need to order it by date and preserve ...
9
votes
1answer
2k views
Order by column should have index or not?
I have added indexes to table which are used for searching result. I am showing results by ASC or DESC order. So that column should have index or not? I have 2 more indexes on that table. How ...
7
votes
5answers
1k views
Oracle sort varchar2 column with special characters last
How can I sort in Oracle a Varchar2 or NVarchar2 column to be in my own custom defined order. Or are any existing options available that will put letters first, then numbers, then all special ...
2
votes
2answers
627 views
Sort by match of LIKE
I wondering how I can implement SQL to get results sorted by best match of a like predicate. I have 100K articles in the database and when user call some items by part of name. I want to show the ...
1
vote
1answer
1k views
Help optimizing MySQL slow query
i'm running a wordpress which needs some mysql optimization, i have a slow query and i would like to get rid of "Using temporary; Using filesort"
query:
EXPLAIN SELECT SQL_CALC_FOUND_ROWS wp_posts.* ...
4
votes
3answers
579 views
Query best possible matches and order them
I'm trying to write a query along these lines:
select *
from tbl
where
col1 = 1
and col2 = 2
and col3 = 3
order by
...
;
I want first all the results where all 3 WHERE ...
2
votes
3answers
138 views
Ordering by date and other columns
I have a one table Q&A like below:
CREATE TABLE `totaalconceptterreinonderhoud`.`acties`
(
`id` int(11) NOT NULL AUTO_INCREMENT COMMENT 'ID',
`type` int(11) NOT NULL DEFAULT '3' ...
8
votes
3answers
768 views
What are the alternatives for an ORDER BY clause in a View?
This question just had to be in this site :)
ORDER BY is forbidden to use in a view, as I understood because of the possibility for multiple order by's when using this view.
I know that there are ...
7
votes
4answers
2k views
How do I do a complex GROUP BY in MySQL?
I have a table that contains several keys into other tables (where each key is comprised of multiple columns). I would like to be able to group rows together that have an equal key, but I don't want ...
3
votes
1answer
2k views
How do I make MySQL auto increment in random order?
I have a table that contains several keys into other tables (where each key is comprised of multiple columns). I would like to create a new column for each key that would be an integer such that ...
6
votes
1answer
440 views
adding an order by to this query returns faster than without, why?
Any ideas on why adding a sort to this query returns considerably faster than without the order by? I would expect the opposite so what could make this happen?
SELECT TOP (500) ...
