SQL Group by & Max

by

In Oracle, SQL Server 2005+ and PostgreSQL 8.4:

SELECT  *
FROM    (
        SELECT  *, ROW_NUMBER() OVER (PARTITION BY alarmId, alarmUnit ORDER BY id DESC) AS rn
        FROM    mytable
        ) q
WHERE   rn = 1

In MySQL:

SELECT  mi.*
FROM    (
        SELECT  alarmId, alarmUnit, MAX(id) AS mid
        FROM    mytable
        GROUP BY
                alarmId, alarmUnit
        ) mo
JOIN    mytable mi
ON      mi.id = mo.mid

In PostgreSQL 8.3 and below:

SELECT  DISTINCT ON (alarmId, alarmUnit) *
FROM    mytable
ORDER BY
        alarmId, alarmUnit, id DESC

More Related Contents:

  1. Group by minimum value in one field while selecting distinct rows
  2. Selecting all corresponding fields using MAX and GROUP BY
  3. SQL not a single-group group function
  4. Reason for Column is invalid in the select list because it is not contained in either an aggregate function or the GROUP BY clause [duplicate]
  5. Return row with the max value of one column per group [duplicate]
  6. Is there any difference between GROUP BY and DISTINCT
  7. How to use count and group by at the same select statement
  8. must appear in the GROUP BY clause or be used in an aggregate function
  9. GROUP BY to combine/concat a column [duplicate]
  10. Is there a Max function in SQL Server that takes two values like Math.Max in .NET?
  11. JOIN two SELECT statement results
  12. Ms Access Query: Concatenating Rows through a query
  13. Simple Query to Grab Max Value for each ID
  14. Group by month and year in MySQL
  15. SQL Query to get column values that correspond with MAX value of another column?
  16. MySQL #1140 – Mixing of GROUP columns
  17. SQL Server Update Group by
  18. PostgreSQL Where count condition
  19. Huge performance difference when using GROUP BY vs DISTINCT
  20. sql group by only rows which are in sequence
  21. GROUP BY without aggregate function
  22. MySQL GROUP BY behavior
  23. Conditional SQL count
  24. SQL: How do you select only groups that do not contain a certain value?
  25. Get values from first and last row per group
  26. How to return a incremental group number per group in SQL
  27. SQL query to group by day
  28. Conversion failed when converting the varchar value ‘simple, ‘ to data type int
  29. How to group by a Calculated Field
  30. How to do a case sensitive GROUP BY?

Leave a Comment