Please start any new threads on our new
site at https://forums.sqlteam.com. We've got lots of great SQL Server
experts to answer whatever question you can come up with.
| Author |
Topic |
|
spikeypaul
Starting Member
2 Posts |
 Posted - 2006-12-08 : 16:27:44
|
| sorry am new to sql and am stuck at what probably is a simple problem but at this time am lost. I have a table with 2 columns. 1st column contains name of a state(USA), 2nd column is of type bit.I have 250 rows and i want to order them by state who has the most rows. For example.column1 column2______________________Georgia 1Georgia 1Georgia 1Texas 1Texas 1Florida 1how can i achieve this? |
|
|
SwePeso
Patron Saint of Lost Yaks
30421 Posts |
Posted - 2006-12-08 : 16:59:45
|
| select column1, count(*)from yourtablenameheregroup by column1order by count(*) desc, column1Didn't your professor show you this?Peter LarssonHelsingborg, Sweden |
 |
|
|
madhivanan
Premature Yak Congratulator
22864 Posts |
|
|
spikeypaul
Starting Member
2 Posts |
Posted - 2006-12-11 : 15:39:20
|
| The result will be something like this: Georgia 3Texas 2Florida 1This is what I initially got when i did the count query. What they want is have that same order (state with most rows) but to display all columns. So, once we figure out that Georgia has the most, then comes texas and florida; show them in that order but all detailed columns.The result should look likeState......y/n.......Country......Zipcode___________________________________________Georgia Y USA 22934Georgia Y USA 39489Georgia Y USA 34452Texas Y USA 11321Texas Y USA 13215Florida Y USA 33131 |
|
|
|
SwePeso
Patron Saint of Lost Yaks
30421 Posts |
Posted - 2006-12-11 : 16:23:50
|
| [code]select o.*from yourinformationtablenamehere oinner join ( select column1, count(*) cnt from yourtablenamehere group by column1 ) u on u.column1 = o.column1order by u.cnt desc, u.column1[/code]Peter LarssonHelsingborg, Sweden |
|
|
|
|
|
|
results order by state with most rows