CODEWITHSUNDEEP
sqlpostgresql
Gavrilo Adamovic
Gavrilo Adamovic

Reputation: 2795

Check if all rows are the same

I have a select query that returns multiple rows, and I want to check if all rows are the same. So something like this:

anything_other_than(123) in (select id from foo)

So, if select id from foo returns 111,222,123,333 the statement above is false, and if select id from foo returns 123,123,123 it's true. How can I achieve this?

Upvotes: 1

Views: 2530

Answers (4)

user330315
user330315

Reputation:

Another option is to use EXISTS with a where condition:

select not exists (select *
                   from the_table
                   where id <> 123);

Upvotes: 3

forpas
forpas

Reputation: 164099

Run this:

select count(distinct id) = 1 and count(*) > 1 from foo;

count(distinct id) will return 1 if all the rows are the same
and count(*) will return the total number of rows.
If this returns true then you have more than 1 rows and all the rows are the same.

Upvotes: 2

Laurenz Albe
Laurenz Albe

Reputation: 246808

A simple solution is to use the = ALL operator:

SELECT 123 = ALL (SELECT id FROM foo);

This solution also stop scanning the result as soon as the first non-matching value is found.

Upvotes: 7

Abhyuday
Abhyuday

Reputation: 41

You can use something like this -

select x, IF(x > 1, "FALSE", "TRUE") from 
(SELECT count(distinct(A.id)) as x FROM foo as A) as B;

Do refer this https://www.w3schools.com/sql/func_mysql_if.asp

Upvotes: -1

Related Questions