How do I create an SQL statement ???

[jerry1941]

The following is a simplification of what I want to do:

> It is for my ham radio logging program
> which you can freely download from my website
> www(dot)wa0h(dot)com
> the 0 in wa0h is the number zero.

I have a very large database which contains 1 table.

Each record in the table contains 3 fields .. city, population, state.

I want a query that produces the following 50 records:

Alabama, city
Arkansas, city
Arizona, city
etc.

ie .. one (large) city (population > 10,000) for each of the 50 states.

If a state has more than 1 large city, I don’t care which city is picked.

The following SQL statement seems logical, but it does not work.

SELECT DISTINCT city, state
FROM table
WHERE population > 10000

Thanks for your time.

[email protected]

[MaximilianMayrhofer]

"SELECT city, state " & _
"FROM table " & _
"GROUP BY state " & _
"HAVING population > 10000;"

If you have more than one state with population greater than 10,000 it will simply return the first one it finds.

[szlamany]

It would be:

Select S1.State,(Select Top 1 S2.City From SomeTable S2
                                  Where S2.State=S1.State
                                  Order by S2.Population Desc)
   From SomeTable S1
   Group by S1.State

You can add to the WHERE clause in that sub-query for the population being greater then 10000 but every state has one so it's not really needed.

@max - how can you select on city and state and only group by one of them?

[MaximilianMayrhofer]

He just said that his database contains only 1 table. Not good db design, but since that's the way it is, your code doesn't apply.

And I only grouped by state because by doing that, it forces the database to return only one city, regardless of how many cities are actually there. If I grouped on both, it would return every city in the state that had a population > 10000.

[szlamany]

@max - your statement is not valid sql syntax. You cannot (actually should not is more proper here) put a field in a having clause without an aggregate function. Your statement doesn't pass that simple syntax qualifier.

Without the HAVING clause it's still not valid - since CITY and STATE need to be in the GROUP BY.

I actually tested my statement to insure that it returned a valid result. My Code Certainly DOES apply!

Create Table SomeTable (State varchar(10), City varchar(10), Population int)
insert into SomeTable values ('CT','Hart',100000)
insert into SomeTable values ('CT','Bpt',150000)
insert into SomeTable values ('CT','Shel',30000)
insert into SomeTable values ('MA','Boston',100000)
insert into SomeTable values ('MA','Spring',100000)
insert into SomeTable values ('MA','Cambridge',20000)
insert into SomeTable values ('NY','NY',1500000)
insert into SomeTable values ('NY','Albany',50000)



Select S1.State,(Select Top 1 S2.City From SomeTable S2
                                  Where S2.State=S1.State
                                  Order by S2.Population Desc)
   From SomeTable S1
   Group by S1.State

and it returned

State      
---------- ----------
CT         Bpt
MA         Spring
NY         NY

(3 row(s) affected)

It more then applies - it actually works!

Attempting your syntax got...

SELECT city, state 
FROM sometable 
GROUP BY state 
HAVING population > 10000

Msg 8121, Level 16, State 1, Line 1
Column 'sometable.Population' is invalid in the HAVING clause because it is 
not contained in either an aggregate function or the GROUP BY clause.

and taking the HAVING clause off showed that the basic SELECT list was failed as well.

SELECT city, state 
FROM sometable 
GROUP BY state 

Msg 8120, Level 16, State 1, Line 1
Column 'sometable.City' is invalid in the select list because it is 
not contained in either an aggregate function or the GROUP BY clause.

Are you using a MS SQL product to test your SQL statements with??

[MaximilianMayrhofer]

Yes! I used the program in SQL Tutorial 2 to test that string. Try this in SQL Tutorial 2:

SELECT item, price FROM items_ordered GROUP BY item HAVING price > 10.00;

Take note of the flashlight entry, and its price. Now do the same thing, but take out the group by and the having clause. You will see two records of the flashlight returned, one of which had a price of 4.50, which is why it wasn't returned in the first instance.

[szlamany]

I already showed you the errors that MS SQL will return with that query.

I have no idea what is behind the SQL Tutorial site - but it must not be MS SQL.

[MaximilianMayrhofer]

In that case, my apologies. I relied on that tutorial site when I was learning SQL, and obviously it was not a very good way to learn.

[si_the_geek]

It is a good site to learn the generic syntax from, but I wouldn't trust the editor that you type queries into - as you have no idea what database system it is using, and it seems to allow little quirks like you have found (which in theory should not work on any major DBMS).

If you look at the text in the "Having" page, it actually explains the same thing that szlamany did (but not as clearly as he did).

[jerry1941]

regarding the (city, state, population) sql statement:

the sql stmt is part of a visual basic 6 program.

the (city-state-pop) problem is an over-simplification of my actual problem, which is harder to explain.

max's solution did produce an error message .. you tried to execute a query that does not include the specified expression (population) as part of an aggregate function.

thanks everyone for helping.

jerry

[Hack]

thanks everyone for helping.

Does this mean your problem is resolved?

[jerry1941]

no .. i still need an sql statement that works in my visual basic program.

jerry

[szlamany]

Ok - I already posted this query

Select S1.State,(Select Top 1 S2.City From SomeTable S2
                                  Where S2.State=S1.State
                                  Order by S2.Population Desc)
   From SomeTable S1
   Group by S1.State

and it's results.

Did you try it? Obviously you need to change the field and table names - but the query worked for me.

Where are you having problems??

[jerry1941]

regarding the solution:

Select S1.State,(Select Top 1 S2.City From SomeTable S2
Where S2.State=S1.State
Order by S2.Population Desc)
From SomeTable S1
Group by S1.State

due to the complexity of the real problem, i need a solution that uses just (1) large table .. having fields (city), (population), (state)

i have been a programmer for 47 years, but i am not an expert in sql.

i was hoping someone could find an sql statement that produces the 50 unique records.

alabama .. city (any city in alabama that has population > 10000)
arkansas .. city
arizona .. city

thanks for your patients in trying to find a solution

jerry

[szlamany]

My sample query is using just one table - I called it SOMETABLE for lack of anything else to call it. And it has just your three columns...

It does call back to that same table for a second sub-query.

But the results are exactly as you described you wanted.

[si_the_geek]

Almost.. the only condition it does not satisfy is population>10000 (instead it finds the one with the largest population in that state), but that can easily be changed later if wanted.

I would guess that you have been confused by the use of SomeTable, S1, and S2. SomeTable is the actual table, S1 and S2 are aliases (fake names so that the database system knows which 'instance' of the table to use).

If you change SomeTable to your table name, does it work for you?

[szlamany]

...You can add to the WHERE clause in that sub-query for the population being greater then 10000 but every state has one so it's not really needed.

I know I left that off - but it really wasn't needed...

[si_the_geek]

I'm definitely inclined to agree, but jerry might have a particular reason for that amount (I thought re-iterating would just make things a little clearer for him).

[jerry1941]

thanks everyone.

SqlString = "SELECT DISTINCT TableS.State, " & _
"(SELECT Top 1 TableC.City " & _
"FROM MyTable TableC " & _
"WHERE TableC.Population > 10000 " & _
"AND TableC.State = TableS.State) " & _
"AS City " & _
"FROM MyTable TableS "

works.

i really appreciate the help.

jerry gentry

springfield, mo.

jerrywa0h (at) sbcglobal.net

www (dot) wa0h (dot) com