# query for a list in a table

**URL:** <https://forum.kx.com/t/query-for-a-list-in-a-table/11491>\
**Category:** Community Support\
**Tags:** kdb-and-q\
**Created:** [February 16, 2017, 10:41pm UTC](https://forum.kx.com/t/query-for-a-list-in-a-table/11491 "2017-02-16T22:41:00Z")\
**Posts on this page:** 8\
**Page:** 1

<div class="post-metadata">

**Author:** ![fausto\_saporito1](https://avatars.discourse-cdn.com/v4/letter/f/f05b48/32.png) [@fausto\_saporito1](https://forum.kx.com/u/fausto_saporito1)\
**Post date:** [February 16, 2017, 10:41pm UTC](https://forum.kx.com/t/query-for-a-list-in-a-table/11491/1 "2017-02-16T22:41:00Z")

</div>

Hello all,

I'm learning Q and KDB, started few days ago.  
I checked several sources, but I am not able to search for a list in a table.

I have this table:

ctable:(v:((0.0 0.0);(1.0 0.0);(0.0 1.0)))

then if I try something like this

select from ctable where (0.0 0.0) in v

I got an empty result

## v

How can I do this select ?

thanks,  
Fausto

---

<div class="post-metadata">

**Author:** ![Krishna1](https://avatars.discourse-cdn.com/v4/letter/k/71e660/32.png) [@Krishna1](https://forum.kx.com/u/Krishna1)\
**Post date:** [February 17, 2017, 9:13am UTC](https://forum.kx.com/t/query-for-a-list-in-a-table/11491/2 "2017-02-17T09:13:00Z")

</div>

q)ctable:(v:((0.0 0.0);(1.0 0.0);(0.0 1.0)))  
Simple trick, not using select -&nbsp;  
q)ctable[ctable?(0f;1f)]

v| 0 1

q)ctable[ctable?(0f;1f)][`v]

0 1f

Kumar

---

<div class="post-metadata">

**Author:** ![Aidan\_O\_Gorman](https://avatars.discourse-cdn.com/v4/letter/a/a87d85/32.png) [@Aidan\_O\_Gorman](https://forum.kx.com/u/Aidan_O_Gorman)\
**Post date:** [February 17, 2017, 9:31am UTC](https://forum.kx.com/t/query-for-a-list-in-a-table/11491/3 "2017-02-17T09:31:00Z")

</div>

You can do this to find all instances of 0 0f in the table:

For example:

q)ctable:(v:(1000?((0.0 0.0);(1.0 0.0);(0.0 1.0))))

q)// Pass each row of column v into the function and check if it’s = (0 0f)

q)ctable where {all x = (0 0f)}each ctable`v&nbsp;

v

* * *

0 0

0 0

0 0

0 0

…

Aidan

---

<div class="post-metadata">

**Author:** ![Jonathon\_McMurr](https://avatars.discourse-cdn.com/v4/letter/j/f17d59/32.png) [@Jonathon\_McMurr](https://forum.kx.com/u/Jonathon_McMurr)\
**Post date:** [February 17, 2017, 9:46am UTC](https://forum.kx.com/t/query-for-a-list-in-a-table/11491/4 "2017-02-17T09:46:00Z")

</div>

Another option:

q)select from ctable where v~:0.0 0.0\<o:p\>\</o:p\>

v\<o:p\>\</o:p\>

—\<o:p\>\</o:p\>

0 0\<o:p\>\</o:p\>

0 0\<o:p\>\</o:p\>

0 0\<o:p\>\</o:p\>

0 0\<o:p\>\</o:p\>

0 0\<o:p\>\</o:p\>

..

Here the ?each-left? adverb is used (http://code.kx.com/wiki/Reference/BackSlashColon) to iterate over the ?v? column\<o:p\>\</o:p\>

\<o:p\> \</o:p\>

Hope this helps\<o:p\>\</o:p\>

Jonathon\<o:p\>\</o:p\>

\<o:p\> \</o:p\>

**From:** aidan.ogorman.aquaq@gmail.com  
**Sent:** 17 February 2017 09:31  
**To:** Kdb+ Personal Developers  
**Subject:** [personal kdb+] Re: query for a list in a table

\<o:p\> \</o:p\>

You can do this to find all instances of 0 0f in the table: \<o:p\>\</o:p\>

\<o:p\> \</o:p\>

For example:\<o:p\>\</o:p\>

\<o:p\> \</o:p\>

q)ctable:(v:(1000?((0.0 0.0);(1.0 0.0);(0.0 1.0))))\<o:p\>\</o:p\>

q)// Pass each row of column v into the function and check if it’s = (0 0f)\<o:p\>\</o:p\>

q)ctable where {all x = (0 0f)}each ctable`v&nbsp;\<o:p\>\</o:p\>

v\<o:p\>\</o:p\>

—\<o:p\>\</o:p\>

0 0\<o:p\>\</o:p\>

0 0\<o:p\>\</o:p\>

0 0\<o:p\>\</o:p\>

0 0\<o:p\>\</o:p\>

…\<o:p\>\</o:p\>

\<o:p\> \</o:p\>

Aidan\<o:p\>\</o:p\>

On Friday, February 17, 2017 at 8:34:41 AM UTC, Fausto Saporito wrote: \<o:p\>\</o:p\>

> Hello all,
> 
> I’m learning Q and KDB, started few days ago.  
> I checked several sources, but I am not able to search for a list in a table.
> 
> I have this table:
> 
> ctable:(v:((0.0 0.0);(1.0 0.0);(0.0 1.0)))
> 
> then if I try something like this
> 
> select from ctable where (0.0 0.0) in v
> 
> I got an empty result
> 
> ## v
> 
> How can I do this select ?
> 
> thanks,  
> Fausto\<o:p\>\</o:p\>

–  
Submitted via Google Groups

---

<div class="post-metadata">

**Author:** ![Krishna1](https://avatars.discourse-cdn.com/v4/letter/k/71e660/32.png) [@Krishna1](https://forum.kx.com/u/Krishna1)\
**Post date:** [February 17, 2017, 9:47am UTC](https://forum.kx.com/t/query-for-a-list-in-a-table/11491/5 "2017-02-17T09:47:00Z")

</div>

Correction - my solution would return ONLY the first match . here is a modification for finding all matches.&nbsp;  
ctable:(v:(1000?((0.0 0.0);(1.0 0.0);(0.0 1.0))))

where (sum over 't:ctable=:enlist(0f;0f))=count (0f;0f)

Kumar

---

<div class="post-metadata">

**Author:** ![fausto\_saporito1](https://avatars.discourse-cdn.com/v4/letter/f/f05b48/32.png) [@fausto\_saporito1](https://forum.kx.com/u/fausto_saporito1)\
**Post date:** [February 17, 2017, 10:05am UTC](https://forum.kx.com/t/query-for-a-list-in-a-table/11491/6 "2017-02-17T10:05:00Z")

</div>

Hello Aidan,

thanks for the reply.  
In this table I should find just one entry for each value I’ll check, so using ? (as proposed in other replies) should be ok.

Fausto

Il giorno venerdì 17 febbraio 2017 10:31:49 UTC+1, [aidan.ogo...@gmail.com](mailto:aidan.ogo...@gmail.com) ha scritto:

> You can do this to find all instances of 0 0f in the table:
> 
> For example:
> 
> q)ctable:(v:(1000?((0.0 0.0);(1.0 0.0);(0.0 1.0))))
> 
> q)// Pass each row of column v into the function and check if it’s = (0 0f)
> 
> q)ctable where {all x = (0 0f)}each ctable`v&nbsp;
> 
> v
> 
> * * *
> 
> 0 0
> 
> 0 0
> 
> 0 0
> 
> 0 0
> 
> …
> 
> Aidan

---

<div class="post-metadata">

**Author:** ![fausto\_saporito1](https://avatars.discourse-cdn.com/v4/letter/f/f05b48/32.png) [@fausto\_saporito1](https://forum.kx.com/u/fausto_saporito1)\
**Post date:** [February 17, 2017, 10:12am UTC](https://forum.kx.com/t/query-for-a-list-in-a-table/11491/7 "2017-02-17T10:12:00Z")

</div>

Hello all,

thanks for the replies!  
I defined this function to operate on that table:

Clookup:{`ctable upsert (v:enlist x);ctable?x}

I simplified my logic in this function avoiding select … so the idea, if the list doesn’t exist in the table, add it and return the row where it is.

My question is: Are sql-like statements good in performance, or is it better avoid using them ?

thanks,  
Fausto

Il giorno venerdì 17 febbraio 2017 09:34:41 UTC+1, Fausto Saporito ha scritto:

> Hello all,
> 
> I’m learning Q and KDB, started few days ago.  
> I checked several sources, but I am not able to search for a list in a table.
> 
> I have this table:
> 
> ctable:(v:((0.0 0.0);(1.0 0.0);(0.0 1.0)))
> 
> then if I try something like this
> 
> select from ctable where (0.0 0.0) in v
> 
> I got an empty result
> 
> ## v
> 
> How can I do this select ?
> 
> thanks,  
> Fausto

---

<div class="post-metadata">

**Author:** ![charlie1](https://avatars.discourse-cdn.com/v4/letter/c/ec9cab/32.png) [@charlie1](https://forum.kx.com/u/charlie1)\
**Post date:** [February 18, 2017, 7:22pm UTC](https://forum.kx.com/t/query-for-a-list-in-a-table/11491/8 "2017-02-18T19:22:00Z")

</div>

I know the thread has moved on, but worth mentioning:

q)select from t where(v)in(v:enlist 0 0.)

extends nicely when searching for multiple values

q)select from t where(v)in(v:(0 0.;0 1.))