CODEWITHSUNDEEP
sqlitemax
snazzybouche
snazzybouche

Reputation: 2426

Select all rows with the MAX value in SQLite?

Say I have a table drafts like the following, where sent_for_review is boolean:

  rowid | title  |  author  | sent_for_review 
 -------|--------|----------|----------------- 
      1 | Draft1 | John Doe |               0 
      2 | Draft2 | John Doe |               0 
      3 | Draft3 | John Doe |               1 
      4 | Draft4 | John Doe |               1

I want to select all drafts that have been sent for review. If there are none, then I want to select all drafts that have not been sent for review.

I tried the following, to try to group by sent_for_review and then selecting just the highest one:

FROM drafts SELECT title WHERE author='John Doe'
GROUP BY sent_for_review HAVING MAX(sent_for_review)

But this only returns Draft3 when Draft3 and Draft4 exist (it should return both)... and doesn't return anything when only Draft1 and Draft2 exist (it should return both).

How can I make it return all maximum values?

Upvotes: 1

Views: 459

Answers (3)

tonypdmtr
tonypdmtr

Reputation: 3225

I believe this is another way to get the same functionality as Lukasz's answer (which is more correct functionally than the accepted answer) but with a simpler query.

create table drafts (title,author,sent_for_review);
insert into drafts values
  ('Draft1','Other Author',0),
  ('Draft2','Other Author',2),
  ('Draft1','John Doe',0),
  ('Draft2','John Doe',0),
  ('Draft3','John Doe',1),
  ('Draft4','John Doe',1);

select rowid,* from drafts a
  where sent_for_review = (select max(sent_for_review)
                            from drafts
                            where author = a.author
                            group by author)
    --and author='John Doe'   --uncomment as needed
    ;

Upvotes: 0

forpas
forpas

Reputation: 164089

With this:

select * from drafts
where 
  author='John Doe' and
  sent_for_review = (select max(sent_for_review) from drafts where author='John Doe')

This query:

select max(sent_for_review) from drafts where author='John Doe'

will return:
1 if there are drafts sent for review and
0 if there are not

Upvotes: 1

Lukasz Szozda
Lukasz Szozda

Reputation: 175696

You could use windowed functions:

WITH cte AS (
  SELECT *, DENSE_RANK() OVER(PARTITION BY author ORDER BY sent_for_review DESC) dr
  FROM drafts
)
SELECT * 
FROM cte
WHERE dr = 1
  AND author='John Doe';

db-fiddle.com demo

Upvotes: 2

Related Questions