Unless I'm grossly misunderstanding your structure, a simple join should work fine:
SELECT stories.vid, body, timestamp, COUNT(votes.id) votecnt FROM stories
INNER JOIN votes ON stories.vid=votes.vid
WHERE stories.lv=1 AND stories.status=1
GROUP BY votes.vid
ORDER BY votecnt DESC
I'm not sure what you mean by
It would be even better, if the first query would sort the table by status = 1 count at first, and then total votes.
since your where statement only uses votes/stories that have status=1.
If an alias is used in an ORDER BY
it must be used on its own, not inside an expression.
If inside any kind of expression it tries to resolve it to a column in the base table sources not as an alias.
So for example
SELECT A AS B
FROM (VALUES (1, 3),
(2, 2),
(3, 1)) V(A, B)
ORDER BY B
Returns (ordered by alias)
+---+
| B |
+---+
| 1 |
| 2 |
| 3 |
+---+
But
SELECT A AS B
FROM (VALUES (1, 3),
(2, 2),
(3, 1)) V(A, B)
ORDER BY B + 0
Returns (Ordered by base table column B
)
+---+
| B |
+---+
| 3 |
| 2 |
| 1 |
+---+
You can of course just wrap the whole thing in a derived table or CTE.
WITH T AS
(
SELECT 1 AS foo, 2 AS bar
UNION ALL
SELECT 10 AS foo, 20 AS bar
)
SELECT *
FROM T
ORDER BY CASE WHEN foo = 1 THEN bar END;
Best Answer
Think of the way telephone directories are (typically) ordered.
If they were generated by an SQL query it would be
The names are ordered by
LastName
first.The secondary column is only used for ordering within groups of people sharing the same last name.