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 |
|
elle39
Starting Member
6 Posts |
 Posted - 2008-06-07 : 13:36:03
|
| I am new to SQL, still learning.If I want to retreive unique records for e.g. VendorName,City and State however no 2 vendors must be of the same city or state.How can I do this?I have used Select and distinct but that still brings unique vendornames in the same state and city. I only need one vendor per city and State.Help! |
|
|
visakh16
Very Important crosS Applying yaK Herder
52326 Posts |
Posted - 2008-06-07 : 14:26:47
|
| SELECT MAX(Vendor),City,State FROM Table GROUP BY City,State |
 |
|
|
elle39
Starting Member
6 Posts |
Posted - 2008-06-07 : 17:11:20
|
| Thanks, though I managed to get this stage. i.e.Select DISTINCT VendorName, VendorCity, VendorStateFrom VendorsGroup by VendorCity, VendorState, VendorNameHowever this produces a result of :XYZ Sacramento CAABC Sacramento CAWhat I want is no two vendors should be listed from the same City and State. |
|
|
|
TG
Master Smack Fu Yak Hacker
6065 Posts |
Posted - 2008-06-07 : 19:56:07
|
| Your DISTINCT is unnecessary because you are GROUPing BY the same columns.Did you try visakh16's solution? - I think it should work...one vendor for each City/State that has vendors.Be One with the OptimizerTG |
|
|
|
elle39
Starting Member
6 Posts |
Posted - 2008-06-07 : 20:25:25
|
| Okay great, got it. Thanks! |
|
|
|
|
|
|
Query to retrieve unique records