0

select max(end_date - start_date) MAX, subquery1.crime_type from crimes, (select crime_type from crimes) subquery1 group by subquery1.crime_type;

table crimes
  [1]: https://i.stack.imgur.com/ufqzz.png
table suspects
  [2]: https://i.stack.imgur.com/GSAoM.png

I tried all I know, tried INNER JOIN, another form of subquery. Something wrong with my code, it outputs all crime_type with correct max sentence, but i need one correct crime_type with max(end_date - start_date) function

MT0
  • 143,790
  • 11
  • 59
  • 117
Nazar Zaychikov
  • 1
  • 1
  • If your query gives the correct result and more then just append `ORDER BY max DESC FETCH FIRST ROW WITH TIES` to find the row(s) with the highest duration and the corresponding types. – MT0 Dec 05 '22 at 13:50

0 Answers0