Menu
In my SQL query I am selecting data with GROUP BY and ORDER BY clauses. The table has the same numbers across multiple rows with different times in each row. So I think I want to apply a GROUP BY clause.
However in the results return the oldest time with the number, but I need the most recent time.
SELECT * FROM TABLE GROUP BY (numbers) ORDER BY time DESC
The query appears as if it should first apply GROUP BY and then ORDER BY... but the results do not appear to work this way.
Is there any way to fix this?
348
Notice the DESC in the GROUP BY clause sorts the status in descending order. And you can also use the ASC explicitly in the GROUP BY clause to sort the groups by status in ascending order.
Order By and Group By Clause in SQL In this SQL tutorial, we will learn how to use Order by and Group By in SQL. Group By in SQL is used to arrange similar data into groups and Order By in SQL is used to sort the data in ascending or descending order.
GROUP ORDER BY orders the data in a group before applying aggregation operations (and before applying GROUP FILTER). Use ASC and DESC keywords to organize column data in ascending or descending order. If no keyword is specified, the default is ascending order.
GROUP BY and ORDER BY are two different things. It is plain wrong that you cannot use them together. GROUP BY is used to tell the DBMS per which group to aggregate the data. In your example you sum gallons per colorComp and colorID .
SELECT * FROM table
WHERE time IN (
SELECT MAX(time)
FROM table
GROUP BY numbers
)
SELECT *
FROM table t
WHERE time = (
SELECT max(time)
FROM table
WHERE t.numbers = numbers
)
work-around is to re-write the query as:
SELECT * FROM (SELECT * FROM table ORDER BY time DESC) AS t GROUP BY numbers;
If you love us? You can donate to us via Paypal or buy me a coffee so we can maintain and grow! Thank you!
Donate Us With
