SELECT MAX in SQL

Question

I have a table1 with info about books in the library (ID, name, publisher, year_of_public, status)

I need get all PUBLISHERS and last YEAR OF PUBLISH for each of the publisher. I've done something like this:

SELECT publisher, year_of_public FROM table 

SELECT MAX (year_of_public) FROM publisher  

but I'm sure that's not right. How to make it better?

Answer 1

To get maximum year_of_public for each publisher, you have to add a group by clause:

SELECT publisher, MAX(year_of_public) AS last_year_of_publish 
FROM publisher 
GROUP BY publisher;

Answer 2

I need get all PUBLISHERS and last YEAR OF PUBLISH for each of the publisher

Assuming you want full rows then you can use correlated subquery :

select p.*
from publisher p
where p.year_of_public = (select max(p1.year_of_public) from publisher p1 where p1.publisher = p.publisher);

Most of databases supports window function then you can use row_number() :

select p.*
from (select p.*, row_number() over (partition by publisher order by year_of_public desc) as seq
      from publisher p
     ) p
where p.seq = 1;