5

What is the equivalent query in KDB Web:

SELECT * FROM TABLE
WHERE (COLA = 'A' AND COLB = 'B') OR (COLA = 'C' AND COLB = 'D')

http://kdbserver:5001/?select fro table where _____________________

N.B.: cola and colb are having string datatype

Debananda
  • 476
  • 1
  • 6
  • 17

4 Answers4

9

You can do:

select from table where ((COLA like "string1")&(COLB like "string2"))|((COLA like "string3")&(COLB like "string4"))
WooiKent Lee
  • 1,301
  • 6
  • 4
4
select from table where ([]colA;colB) in ([]colA:`A`C;colB:`B`D)
Connor Gervin
  • 935
  • 5
  • 16
  • for strings.. select from table where ([]\`$colA;\`$colB) in ([]colA:\`A\`C;colB:\`B\`D) – Connor Gervin Jan 29 '16 at 15:36
  • How does this perform against the other answers? – Alexander Balabin May 27 '16 at 12:25
  • I'm seeing ~2x quicker over a 100m rows `q)n:100000000;` `q)tab:([]a:n?\`IBM\`AAPL\`MSFT\`GOOG;b:n?1 2 3 4);` `q)\ts t1:select from tab where ([]a;b) in ([]a:\`AAPL\`GOOG\`IBM\`IBM\`GOOG;b:1 2 3 4 5);` `7318 671090016j` `q)\ts t2:select from tab where ((a like "AAPL")&(b=1))|((a like "GOOG")&(b=2))|((a like "IBM")&(b=3))|((a like "IBM")&(b=4))|((a like "GOOG")&(b=5));` `12462 536873248j` `q)r2~r1;` `1b` – Connor Gervin May 27 '16 at 16:59
2

Connor is right and his answer is quite efficient. Just want to add a version with list operation instead of table:

tab:([]cola:("aaa";"bbb";"ccc");colb:("ddd";"eee";"fff"))
select from tab where (flip(cola;colb))in\:(("aaa";"ddd");("bbb";"eee"))

Execution speed is almost identical with Connor's one

Community
  • 1
  • 1
Charles Lin
  • 624
  • 1
  • 5
  • 5
0

Sometime I prefer changing type to symbol to get results,
say, 

tab1:([]a:string 10?`2;b:string 10?`2; c: string 10?`2)

--

select from tab1 where (((`$a)=`$"ci") & ((`$b)=`$"lf")) or (((`$a)=`$"en") & ((`$b)=`$"dl"))
Pratyush
  • 71
  • 1
  • 10
  • Note for columns with many uniques, this will bloat the internalized symbol-list of the process – skeevey Dec 19 '16 at 18:38