Ms-access – Select Unique Fields with Max Values in Each

distinctms accessselecttimestamp

I have a table with names, numbers, and timestamps on the data. I want to know how to select a unique instance of each name based on how recent the timestamp is.

Addy (text), NW (number), UDate (timestamp), Stance (text).

I want to pull only one instance of Addy, and I want the one I pull to have the most recent UDate value. I'd like to make this into a separate table so that I've got the original for historical information and a more usable table with the most recent intel.

I'm using Microsoft Access 2013.

Best Answer

This type of queries are sometimes called "greatest n per group" (there is even a tag in StackOverflow and DBA.SE). In this question, you want one row per group.

A common solution is to use window functions (but these ar enot available in Access) and another is to use GROUP BY in a derived table and then join back to the original table. Like this:

SELECT t.*
FROM 
    TableName AS t
  INNER JOIN
    ( SELECT Addy, MAX(UDate) AS UDate
      FROM TableName
      GROUP BY Addy
    ) AS grp
  ON  grp.Addy = t.Addy
  AND grp.UDate = t.UDate ;

Related Solutions

Mysql – Getting unique names when the IDs are different (DISTINCT ?)

Here is the query

SELECT id,name FROM
(SELECT name,MAX(id) id FROM `mytable` GROUP BY name) A
ORDER BY id DESC LIMIT 2;

Here is some sample data

mysql> use test
Database changed
mysql> drop table if exists mytable;
Query OK, 0 rows affected (0.03 sec)

mysql> create table mytable
    -> (id int not null auto_increment,
    -> name varchar(20),primary key (id));
Query OK, 0 rows affected (0.12 sec)

mysql> INSERT INTO mytable (name) VALUES
    -> ('John'),('John'),('Peter'),('Mike'),('Mike');
Query OK, 5 rows affected (0.07 sec)
Records: 5  Duplicates: 0  Warnings: 0

mysql> SELECT * FROM mytable;
+----+-------+
| id | name  |
+----+-------+
|  1 | John  |
|  2 | John  |
|  3 | Peter |
|  4 | Mike  |
|  5 | Mike  |
+----+-------+
5 rows in set (0.00 sec)

mysql> SELECT id,name FROM
    -> (SELECT name,MAX(id) id FROM `mytable` GROUP BY name) A
    -> ORDER BY id DESC LIMIT 2;
+------+-------+
| id   | name  |
+------+-------+
|    5 | Mike  |
|    3 | Peter |
+------+-------+
2 rows in set (0.00 sec)

mysql>

Mysql – How to get the current and next greater value in one select

Doing a JOIN is one thing you might need.

SELECT l.id, l.time, r.time FROM 
    idtimes AS l LEFT JOIN idtimes AS r ON l.id = r.id

I suppose the outer join is deliberate, and you want to be getting nulls. More on that later.

WHERE l.time < r.time ORDER BY l.id ASC, l.time ASC;

You only want the r. row that has the lowest (MIN) time that is higher than the l.time. That is the place where you need subquerying.

WHERE r.time = (SELECT MIN(time) FROM idtimes r2 where r2.id = l.id AND r2.time > l.time)

Now to the nulls. If "there is no next-higher time", then the SELECT MIN() will evaluate to null (or worse), and that itself never compares equal to anything, so your WHERE clause will never be satisfied, and the "highest time" for each ID, could never appear in the result set.

You solve it by eliminating your JOIN, and moving the scalar subquery into the SELECT list :

SELECT id, time, 
    (SELECT MIN(time) FROM idtimes sub 
        WHERE sub.id = main.id AND sub.time > main.time) as nxttime
  FROM idtimes AS main

Best Answer

Related Solutions

Mysql – Getting unique names when the IDs are different (DISTINCT ?)

Mysql – How to get the current and next greater value in one select

Related Question