How to select rows that has distinct value in one field and sorted by another field in MySQL?

Question

I have a table like this:

-----------------------------
id | uid | year | other | many | fields
-----------------------------
1  | 1   | 2010 | blabla ...
2  | 2   | 1999 | blablabla ...
3  | 3   | 2011 | bla ...
4  | 1   | 2006 | blablablabla ...
...
-----------------------------

What I want is to select all fields in all records that

1. has distinct uid and only returns the last record (i.e., has the highest id)

2. the results are sorted by year

An example of returned records like:

-----------------------------
id | uid | year | other | many | fields
-----------------------------
2  | 2   | 1999 | blablabla ...
4  | 1   | 2006 | blablablabla ...
3  | 3   | 2011 | bla ...
-----------------------------

It looks like similar to question How to use DISTINCT and ORDER BY in same SELECT statement? but I couldn't get it work.

I tried SELECT * FROM table GROUP BY uid ORDER BY MAX(id) DESC, MAX(year), but it seems neither sorting id nor year.

update:

Thanks for all solutions, here is the new problem: I'm actually developing plugin in Discuz, and it doesn't allow sub queries for security reason, is there any way to use only one select? Or any workaround in Discuz plugin development? Thanks again.

Answer 1

To my knowledge, I can give you two approaches,

(1) Mysql specific

SELECT * FROM (SELECT * FROM `table_name` ORDER BY `id` DESC) tbl
GROUP BY `uid` ORDER BY `year`

Note: In Mysql, we don't have to apply GROUP BY to every column in order to get its non-aggregate value and instead, only the first row is returned.

(2) For any RDBMS

SELECT * FROM table_name 
WHERE id IN ( 
    SELECT Max(id) FROM table_name
    GROUP BY uid
)
ORDER BY year

OR

SELECT tbl1.id, tbl1.uid, tbl1.year, tbl1.other 
FROM table_name tbl1
INNER JOIN (
    SELECT Max(id) id FROM table_name
    GROUP BY uid
) tbl2
ON tbl1.id = tbl2.id
ORDER BY tbl1.year

All of the above statements will yield the same result as below:

----------------------------
| id | uid | year | other | 
-----+-----+------+------------
| 2  | 2   | 1999 | blablabla ...
| 4  | 1   | 2006 | blablablabla ...
| 3  | 3   | 2011 | bla ...
-----------------------------

Answer 2

you can try this one

select distinct * from test where id IN (select MAx(id) id from test GROUP BY uid) order by year 

test=>table name; it will give out put as

Answer 3

The following query might do the job done.

SELECT 
  your_table.id,
  your_table.uid,
  your_table.year,
  your_table.other
FROM your_table 
INNER JOIN 
(
    SELECT 
    uid,
    MAx(id) max_id
    FROM your_table
    GROUP BY uid
) t
ON your_table.id = t.max_id AND your_table.uid = t.uid
ORDER BY your_table.id, your_table.year;

The above query will return the records corresponding to maximum id under same uid and sorts the records in ascending order of id and year.

SQL FIDDLE is not working

---

TEST DATA:

DROP TABLE IF EXISTS `your_table`;
CREATE TABLE `your_table` (
  `id` int(11) NOT NULL AUTO_INCREMENT,
  `uid` int(50) NOT NULL,
  `year` int(11) NOT NULL,
  `other` varchar(100) CHARACTER SET utf8 NOT NULL,
  PRIMARY KEY (`id`)
);
INSERT INTO `your_table` VALUES ('1', '1', '2010', 'blabla ...');
INSERT INTO `your_table` VALUES ('2', '2', '1999', 'blablabla...');
INSERT INTO `your_table` VALUES ('3', '3', '2011', 'bla ...');
INSERT INTO `your_table` VALUES ('4', '1', '2006', 'blablablabla....');

Output:

Running the above query on these test data you will get the following output.

id     uid      year     other 

2       2       1999    blablabla...
3       3       2011    bla ...
4       1       2006    blablablabla....