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 |
|
mrkalakota
Starting Member
3 Posts |
 Posted - 2005-05-23 : 12:18:47
|
| Somebody pls. help me.I have a table called Facility which has Id, Name, City, State, Add1, Add2 columns and Id has a unique constraint.I want Add1, Add2 columns based on the combination of (Name, City, State) having count = 1 if their count is > 1 then I dont need that record.ThanksMadhu |
|
|
AjarnMark
SQL Slashing Gunting Master
3246 Posts |
Posted - 2005-05-23 : 18:16:52
|
| Well, if you just are trying to do a query and only want those facilities that have only one record in your system, then something like:SELECT Name, City, State, MIN(Add1), MIN(Add2)FROM FacilityGROUP BY Name, City, StateHAVING COUNT(*) = 1But is that really what you want? Or are you trying to purge you system of duplicate rows? In that case, look into a Remove Duplicates query (I believe there is a wizard for this). Or, if you want the "first" record regardless of how many a facility has, I believe access has a FIRST() function, but beware that it's a false concept in relational databases. Access may have a way to make it come up, probably based on some timestamp or sequential ID, but in regular relational databases, there is no such thing as "first" without an ORDER BY statement.---------------------------EmeraldCityDomains.com |
 |
|
|
mrkalakota
Starting Member
3 Posts |
Posted - 2005-05-25 : 10:57:50
|
| Wow AjarnMark.. So simple. It works fine. thats what I want.. Thank you very much.Madhu |
|
|
|
|
|
|
|
|
Sql Query Help ( MS Access)