Ttdfjt

How would one muzzle a full grown polar bear in the 13th century?

Term for maladaptive animal behavior that will lead to their demise?

Was there a shared-world project before "Thieves World"?

What is the relationship between spectral sequences and obstruction theory?

What do the phrase "Reeyan's seacrest" and the word "fraggle" mean in a sketch?

How exactly does Hawking radiation decrease the mass of black holes?

Was is really necessary for the Lunar module LM to have 2 stages?

Is there really no use for MD5 anymore?

A Strange Latex Symbol

Was there a Viking Exchange as well as a Columbian one?

simple conditions equation

What route did the Hindenburg take when traveling from Germany to the U.S.?

How can I change the color of a part of a line?

Why don't other Westeros houses use wildfire?

Packing rectangles: Does rotation ever help?

Realistic Necromancy?

How to creep the reader out with what seems like a normal person?

Apply MapThread to all but one variable

How can the Zone of Truth spell be defeated without the caster knowing?

What is the strongest case that can be made in favour of the UK regaining some control over fishing policy after Brexit?

Meaning of Bloch representation

How do I deal with a coworker that keeps asking to make small superficial changes to a report, and it is seriously triggering my anxiety?

Binary Numbers Magic Trick

Will a top journal at least read my introduction?

SQL Query not selecting all points that it should?

Changing symbology of selected features only using ArcMap?SQL-query should return GeoJSON FeatureCollectionSelecting all cells not containing a number in SQLSql Query in PostgresqlHow to make a SQL query that would sum multiple records in ArcGIS?Link Excel Table to Access/Arcmap Personal Geodatabase TableSub-Query Using “NOT IN” (Selection Not Correct)Clipping large shapefile in ArcMap and .dbf size remains same?Selecting polygon feature classes with 1 record in ArcSDE using SQL query?Selecting records by SQL Query and saving SUM or COUNT result into differnt table

.everyoneloves__top-leaderboard:empty,.everyoneloves__mid-leaderboard:empty,.everyoneloves__bot-mid-leaderboard:empty margin-bottom:0;

2

I am working on a map of 60+ monitoring wells and I plotted them through an excel data sheet and a few tools. In that data sheet, I have Benzene concentrations associated with almost each point. I need to have the ones that are above SHS to be green and the ones that are below SHS to be red. I have tried setting up SQL Query a few times and also tried selecting by attribute, but every time it only selects one or two points when it should be selecting 12 points (7, 17, 23, 25, 37, 58, 140, 180, 230, 270, 280, 1100), I've tried a few different queries including "Benzene" > '5', which selected 2 points (7 and 58), "Benzene" >= '7', which selected one point (7). I tried selecting by attribute and only 7 was selected every time. Also, when I've tried doing the opposite in label editor ( "Benzene" <= '5', it says it is a valid expression, but all the numbers above 5 are still showing up green).

What am I doing wrong?

My benzene column is a String.

arcgis-desktop arcmap sql fields-attributes query

share|improve this question

edited Apr 24 at 13:05

PolyGeo♦

54.1k1782247

asked Apr 24 at 12:17

mv9916mv9916

111

New contributor

mv9916 is a new contributor to this site. Take care in asking for clarification, commenting, and answering.
Check out our Code of Conduct.

add a comment | 

2

I am working on a map of 60+ monitoring wells and I plotted them through an excel data sheet and a few tools. In that data sheet, I have Benzene concentrations associated with almost each point. I need to have the ones that are above SHS to be green and the ones that are below SHS to be red. I have tried setting up SQL Query a few times and also tried selecting by attribute, but every time it only selects one or two points when it should be selecting 12 points (7, 17, 23, 25, 37, 58, 140, 180, 230, 270, 280, 1100), I've tried a few different queries including "Benzene" > '5', which selected 2 points (7 and 58), "Benzene" >= '7', which selected one point (7). I tried selecting by attribute and only 7 was selected every time. Also, when I've tried doing the opposite in label editor ( "Benzene" <= '5', it says it is a valid expression, but all the numbers above 5 are still showing up green).

What am I doing wrong?

My benzene column is a String.

arcgis-desktop arcmap sql fields-attributes query

share|improve this question

edited Apr 24 at 13:05

PolyGeo♦

54.1k1782247

asked Apr 24 at 12:17

mv9916mv9916

111

New contributor

mv9916 is a new contributor to this site. Take care in asking for clarification, commenting, and answering.
Check out our Code of Conduct.

add a comment | 

I am working on a map of 60+ monitoring wells and I plotted them through an excel data sheet and a few tools. In that data sheet, I have Benzene concentrations associated with almost each point. I need to have the ones that are above SHS to be green and the ones that are below SHS to be red. I have tried setting up SQL Query a few times and also tried selecting by attribute, but every time it only selects one or two points when it should be selecting 12 points (7, 17, 23, 25, 37, 58, 140, 180, 230, 270, 280, 1100), I've tried a few different queries including "Benzene" > '5', which selected 2 points (7 and 58), "Benzene" >= '7', which selected one point (7). I tried selecting by attribute and only 7 was selected every time. Also, when I've tried doing the opposite in label editor ( "Benzene" <= '5', it says it is a valid expression, but all the numbers above 5 are still showing up green).

What am I doing wrong?

My benzene column is a String.

arcgis-desktop arcmap sql fields-attributes query

share|improve this question

edited Apr 24 at 13:05

PolyGeo♦

54.1k1782247

asked Apr 24 at 12:17

mv9916mv9916

111

New contributor

mv9916 is a new contributor to this site. Take care in asking for clarification, commenting, and answering.
Check out our Code of Conduct.

share|improve this question

edited Apr 24 at 13:05

PolyGeo♦

54.1k1782247

asked Apr 24 at 12:17

mv9916mv9916

111

New contributor

mv9916 is a new contributor to this site. Take care in asking for clarification, commenting, and answering.
Check out our Code of Conduct.

share|improve this question

edited Apr 24 at 13:05

PolyGeo♦

54.1k1782247

asked Apr 24 at 12:17

mv9916mv9916

111

New contributor

mv9916 is a new contributor to this site. Take care in asking for clarification, commenting, and answering.
Check out our Code of Conduct.

edited Apr 24 at 13:05

PolyGeo♦

54.1k1782247

edited Apr 24 at 13:05

PolyGeo♦

54.1k1782247

asked Apr 24 at 12:17

mv9916mv9916

111

New contributor

mv9916 is a new contributor to this site. Take care in asking for clarification, commenting, and answering.
Check out our Code of Conduct.

asked Apr 24 at 12:17

mv9916mv9916

111

2 Answers
2

active

oldest

votes

4

When comparing strings, the comparison is done character by character, so 2 is "bigger" - or rather "after" 12 (2 vs 1).

You would need to change the datatype in your expression

int("Benzene") >= 7

You might have to use CInt() instead of int()

share|improve this answer

answered Apr 24 at 12:28

JGHJGH

13.9k21339

• 
  
  
  

  
  

  
  
  
  
  
  

  
  
  I got an error: 'A name was started with an invalid character'
  
  – mv9916
  Apr 24 at 12:43
  

  
  
  
  

add a comment | 

4

Your problem is the data type as String.

You are trying to use it like numerical value but in string the "number" order is "alphabetical" meaning that "2" for exemple come after 11 (or that 2 > 11 is evaluated as true...).

If you sort your column in the attribute table you will see that all number starting by "1" are on top (like 1, 10, 1125,...) then number starting by "2" then ...

As consequence you can't use operator like > or < with number set as string and expect a meaningful result.

The solution is to change the data type to numeric (either when you import your data or by creating a new field and use the field calculator to convert your string number to number number)

share|improve this answer

answered Apr 24 at 12:29

J.RJ.R

3,711222

• 
  
  
  

  
  

  
  
  
  
  
  

  
  
  Thank you! When I used the 'Make XY Event Layer' tool it automatically set it as a string. Didn't even think of that. Thank you!
  
  – mv9916
  Apr 24 at 12:30
  

  
  
  
  


• 
  
  
  

  
  

  
  
  
  
  
  

  
  
  Just realized why it's a string. Most of my values for Benzene are <1 and they won't work in a double or long integer
  
  – mv9916
  Apr 24 at 12:39
  

  
  
  
  


• 
  
  
  

  
  3
  

  
  
  
  
  
  

  
  
  @mv9916, a Double should work with any number, doesn't matter if it has decimals or not.
  
  – klewis
  Apr 24 at 16:30
  

  
  
  
  

add a comment | 

Your Answer

StackExchange.ready(function()
var channelOptions =
tags: "".split(" "),
id: "79"
;
initTagRenderer("".split(" "), "".split(" "), channelOptions);

StackExchange.using("externalEditor", function()
// Have to fire editor after snippets, if snippets enabled
if (StackExchange.settings.snippets.snippetsEnabled)
StackExchange.using("snippets", function()
createEditor();
);

else
createEditor();

);

function createEditor()
StackExchange.prepareEditor(
heartbeatType: 'answer',
autoActivateHeartbeat: false,
convertImagesToLinks: false,
noModals: true,
showLowRepImageUploadWarning: true,
reputationToPostImages: null,
bindNavPrevention: true,
postfix: "",
imageUploader:
brandingHtml: "Powered by u003ca class="icon-imgur-white" href="https://imgur.com/"u003eu003c/au003e",
contentPolicyHtml: "User contributions licensed under u003ca href="https://creativecommons.org/licenses/by-sa/3.0/"u003ecc by-sa 3.0 with attribution requiredu003c/au003e u003ca href="https://stackoverflow.com/legal/content-policy"u003e(content policy)u003c/au003e",
allowUrls: true
,
onDemand: true,
discardSelector: ".discard-answer"
,immediatelyShowMarkdownHelp:true
);



);

mv9916 is a new contributor. Be nice, and check out our Code of Conduct.

draft saved

draft discarded

Sign up or log in

StackExchange.ready(function ()
StackExchange.helpers.onClickDraftSave('#login-link');
);

Sign up using Google

Sign up using Facebook

Sign up using Email and Password

Post as a guest

Name

Email

Required, but never shown

StackExchange.ready(
function ()
StackExchange.openid.initPostLogin('.new-post-login', 'https%3a%2f%2fgis.stackexchange.com%2fquestions%2f320722%2fsql-query-not-selecting-all-points-that-it-should%23new-answer', 'question_page');

);

Post as a guest

Name

Email

Required, but never shown

4

When comparing strings, the comparison is done character by character, so 2 is "bigger" - or rather "after" 12 (2 vs 1).

You would need to change the datatype in your expression

int("Benzene") >= 7

You might have to use CInt() instead of int()

share|improve this answer

answered Apr 24 at 12:28

JGHJGH

13.9k21339

• 
  
  
  

  
  

  
  
  
  
  
  

  
  
  I got an error: 'A name was started with an invalid character'
  
  – mv9916
  Apr 24 at 12:43
  

  
  
  
  

add a comment | 

4

When comparing strings, the comparison is done character by character, so 2 is "bigger" - or rather "after" 12 (2 vs 1).

You would need to change the datatype in your expression

int("Benzene") >= 7

You might have to use CInt() instead of int()

share|improve this answer

answered Apr 24 at 12:28

JGHJGH

13.9k21339

• 
  
  
  

  
  

  
  
  
  
  
  

  
  
  I got an error: 'A name was started with an invalid character'
  
  – mv9916
  Apr 24 at 12:43
  

  
  
  
  

add a comment | 

When comparing strings, the comparison is done character by character, so 2 is "bigger" - or rather "after" 12 (2 vs 1).

You would need to change the datatype in your expression

int("Benzene") >= 7

You might have to use CInt() instead of int()

share|improve this answer

answered Apr 24 at 12:28

JGHJGH

13.9k21339

int("Benzene") >= 7

share|improve this answer

answered Apr 24 at 12:28

JGHJGH

13.9k21339

answered Apr 24 at 12:28

JGHJGH

13.9k21339

answered Apr 24 at 12:28

JGHJGH

13.9k21339

4

Your problem is the data type as String.

You are trying to use it like numerical value but in string the "number" order is "alphabetical" meaning that "2" for exemple come after 11 (or that 2 > 11 is evaluated as true...).

If you sort your column in the attribute table you will see that all number starting by "1" are on top (like 1, 10, 1125,...) then number starting by "2" then ...

As consequence you can't use operator like > or < with number set as string and expect a meaningful result.

The solution is to change the data type to numeric (either when you import your data or by creating a new field and use the field calculator to convert your string number to number number)

share|improve this answer

answered Apr 24 at 12:29

J.RJ.R

3,711222

• 
  
  
  

  
  

  
  
  
  
  
  

  
  
  Thank you! When I used the 'Make XY Event Layer' tool it automatically set it as a string. Didn't even think of that. Thank you!
  
  – mv9916
  Apr 24 at 12:30
  

  
  
  
  


• 
  
  
  

  
  

  
  
  
  
  
  

  
  
  Just realized why it's a string. Most of my values for Benzene are <1 and they won't work in a double or long integer
  
  – mv9916
  Apr 24 at 12:39
  

  
  
  
  


• 
  
  
  

  
  3
  

  
  
  
  
  
  

  
  
  @mv9916, a Double should work with any number, doesn't matter if it has decimals or not.
  
  – klewis
  Apr 24 at 16:30
  

  
  
  
  

add a comment | 

4

Your problem is the data type as String.

You are trying to use it like numerical value but in string the "number" order is "alphabetical" meaning that "2" for exemple come after 11 (or that 2 > 11 is evaluated as true...).

If you sort your column in the attribute table you will see that all number starting by "1" are on top (like 1, 10, 1125,...) then number starting by "2" then ...

As consequence you can't use operator like > or < with number set as string and expect a meaningful result.

The solution is to change the data type to numeric (either when you import your data or by creating a new field and use the field calculator to convert your string number to number number)

share|improve this answer

answered Apr 24 at 12:29

J.RJ.R

3,711222

• 
  
  
  

  
  

  
  
  
  
  
  

  
  
  Thank you! When I used the 'Make XY Event Layer' tool it automatically set it as a string. Didn't even think of that. Thank you!
  
  – mv9916
  Apr 24 at 12:30
  

  
  
  
  


• 
  
  
  

  
  

  
  
  
  
  
  

  
  
  Just realized why it's a string. Most of my values for Benzene are <1 and they won't work in a double or long integer
  
  – mv9916
  Apr 24 at 12:39
  

  
  
  
  


• 
  
  
  

  
  3
  

  
  
  
  
  
  

  
  
  @mv9916, a Double should work with any number, doesn't matter if it has decimals or not.
  
  – klewis
  Apr 24 at 16:30
  

  
  
  
  

add a comment | 

Your problem is the data type as String.

You are trying to use it like numerical value but in string the "number" order is "alphabetical" meaning that "2" for exemple come after 11 (or that 2 > 11 is evaluated as true...).

If you sort your column in the attribute table you will see that all number starting by "1" are on top (like 1, 10, 1125,...) then number starting by "2" then ...

As consequence you can't use operator like > or < with number set as string and expect a meaningful result.

The solution is to change the data type to numeric (either when you import your data or by creating a new field and use the field calculator to convert your string number to number number)

share|improve this answer

answered Apr 24 at 12:29

J.RJ.R

3,711222

share|improve this answer

answered Apr 24 at 12:29

J.RJ.R

3,711222

answered Apr 24 at 12:29

J.RJ.R

3,711222

answered Apr 24 at 12:29

J.RJ.R

3,711222