Select Rows Having 2 Columns Equal Value

Select rows having 2 columns equal value

Question 1 query:

SELECT ta.C1
,ta.C2
,ta.C3
,ta.C4
FROM [TableA] ta
WHERE (SELECT COUNT(*)
FROM [TableA] ta2
WHERE ta.C2=ta2.C2
AND ta.C3=ta2.C3
AND ta.C4=ta2.C4)>1

SQL query to select rows having 2 columns equal value

You can use Group By with conditional aggregation in Having clause to consider only those where published has never been zero.

SELECT id
FROM table_name
GROUP BY id
HAVING SUM(published = 0) = 0

Alternatively, as @Gordon Linoff suggested, you can use the following as well:

SELECT id
FROM table_name
GROUP BY id
HAVING MIN(published) > 0

SQL Select rows where two columns are equal, but a third is different

Assuming there are no nulls, your numbers are integers and your strings have no spaces needing to be trimmed:

CREATE TABLE [dbo].[Mailbox](
[ID] [int] NOT NULL,
[MailboxId] [varchar](10) NULL,
[Connection] [int] NULL,
[EmailId] [int] NULL,
CONSTRAINT [PK_Mailbox] PRIMARY KEY CLUSTERED
(
[ID] ASC
)WITH (PAD_INDEX = OFF, STATISTICS_NORECOMPUTE = OFF, IGNORE_DUP_KEY = OFF, ALLOW_ROW_LOCKS = ON, ALLOW_PAGE_LOCKS = ON) ON [PRIMARY]
) ON [PRIMARY]

INSERT INTO mailbox (ID, mailboxid, connection, emailid) Values (0001, 'M0001', 1, 9999);
INSERT INTO mailbox (ID, mailboxid, connection, emailid) Values (0002, 'M0001', 2, 9999);
INSERT INTO mailbox (ID, mailboxid, connection, emailid) Values (0003, 'M0002', 1, 9998);
INSERT INTO mailbox (ID, mailboxid, connection, emailid) Values (0004, 'M0002', 1, 9998);
INSERT INTO mailbox (ID, mailboxid, connection, emailid) Values (0005, 'M0002', 2, 9997);
INSERT INTO mailbox (ID, mailboxid, connection, emailid) Values (0006, 'M0003', 1, 9996);


select m1.Id, m2.id
from Mailbox m1, Mailbox m2
Where m1.id <> m2.id
AND M1.mailboxid = m2.mailboxid
and m1.emailid = m2.emailid
and m1.connection <> m2.connection
and m1.id < m2.id

should give you this:

Id  id
1 2

You can trim the Mailbox id if necessary and add the null checks, if that is a problem as well.

Select rows having 2 columns equal value in laravel Query builder

What you need is a DB::raw expression:

DB::table('users')
->where('username', '=', DB::raw('lastname'))
->get();

The only thing DB::raw actually does is to tell the query interpreter not to treat 'lastname' like any other string value but just interpolate it in the SQL as it is.

http://laravel.com/docs/queries#raw-expressions

Select rows having multiple columns of same value

Sample data..

CREATE TABLE foo
AS
SELECT id,
trunc(random()*10) AS col1,
trunc(random()*10) AS col2,
trunc(random()*10) AS col3,
trunc(random()*10) AS col4
FROM generate_series(1,1e6)
AS gs(id);

Using ALL

This method is massively shorter, but you still have to type all of the column names once.

SELECT * FROM foo
WHERE col1 = ALL(ARRAY[col2,col3,col4]);

Dynamic sql..

SELECT format(
'SELECT * FROM foo WHERE %s;',
(
SELECT string_agg('col1 = col'||id, ' AND ')
FROM generate_series(2,4) AS id
)
);

Select all rows where two columns have the same value?

The simplest method are probably window functions:

select t.*
from (select t.*,
count(*) over (partition by id, version) as cnt
from t
) t
where cnt >= 2;

If you have an index on (id, version) (or (version, id)), then the database engine should be able to take advantage of that.

How can select rows where two columns do not equal a value?

So then why not try a NOT?

SELECT *
FROM yourtable
WHERE NOT (category = 'Fruit' AND subcategory IN ('Orange', 'Apple'))

SQL - select rows that have the same value in two columns

Since you mentioned names can be duplicated, and that a duplicate name still means is a different person and should show up in the result set, we need to use a GROUP BY HAVING COUNT(*) > 1 in order to truly detect dupes. Then join this back to the main table to get your full result list.

Also since from your comments, it sounds like you are wrapping this into a view, you'll need to separate out the subquery.

CREATE VIEW DUP_CARDS
AS
SELECT CARDNUMBER, MEMBERTYPE
FROM mytable t2
GROUP BY CARDNUMBER, MEMBERTYPE
HAVING COUNT(*) > 1

CREATE VIEW DUP_ROWS
AS
SELECT t1.*
FROM mytable AS t1
INNER JOIN DUP_CARDS AS DUP
ON (T1.CARDNUMBER = DUP.CARDNUMBER AND T1.MEMBERTYPE = DUP.MEMBERTYPE )

SQL Fiddle Example



Related Topics



Leave a reply



Submit