As we all know, the ORDER BY clause is processed after the SELECT clause, so a column alias in the SELECT clause can be used. However, I find that I can’t use the aliased column in a calculation in the ORDER BY clause. I know there are alternative ways of doing this particular query, and I know that this is
Tag: sql-order-by
How to get the first row per group?
I have a query like this: The result looks like this: Now I want to get the first row for each category_id. So it must be the biggest num and its business_id. So the expected result would be: How can I do that? Answer if your MySQL version support ROW_NUMBER + window function, you can try to use ROW_NUMBER to
How to select records from a Postgres table using an ORDER BY statement on a jsonb column that points to another table
I have two tables, characteristics and students. characteristics students The characteristics table has three columns where the id column is an auto-increment field. The name denotes the name of a specific characteristic and the value indicates the respective information about the characteristic. The students…
Could you tell me why my ORDER BY DESC function is not working properly?
the column is float not null Answer You have a numeric column called [%] and you are naming your calculated column [%] too. The type of your calculated value if nvarchar. As pointed out by @Tarik, it looks like SQL Server is ordering the result by the calculated column (alias). I think it would be much more c…
MySQL Query Returns Different Results
I’m having a strange problem with the following query: Sometimes I get zero, one, two or three results. I should always get three results. What could be causing this? Answer As pointed out by Solarflare, GROUP_CONCAT() could produce your team string in a random order, e.g. ‘Van Williams & Dere…
Group items by 2 columns or similarity with another column
Let’s say we have a Sqlite table containing: How to group by h=CONCAT(city,age) but also add in the group the users that have a column foo identical to someone in the group? As seen in Group items by 2 columns, here is how to group by h: How to add this second condition on foo? Example: here Alice and B…
SQL query for entries of last N jobs
I have the following table: etc, so basically jobs keep adding entries concurrently I am looking for an efficient query to return all entries of the latest (by time) N jobs So find the first (by time) entry for each job, then using that get the latest N jobs with all their entries So for the above table and N…
How to determine the order of the result from my postgres query?
I have the following query: This is giving me the result: Sometimes the order changes of the result changes from SOH -> SOC or from SOC -> SOH. I’m trying to modify my query so I always get SOH first and than SOC.. How can I achieve this? Answer You have two times that are identical. The order by …
Error when SQL Server grand total comes out on first row instead of at table end
The following query gets row and column totals. When I add ORDER BY, the column total appears in the first row instead of the bottom row. What could be your suggested solution? Answer you can use grouping which returns 1 if the column is part of aggregation and you can use it in your order by:
DISTINCT and ORDER BY in the same command without using the order by variable
I need to select the recent 5 games the user has gone into but I don’t want there to be duplicates. So I need a SQL line that will take them in order from most recent to least but also make sure there are no distinct. To order them I have a time variable, but if I use: this removes