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 |
|
esilva002
Starting Member
10 Posts |
 Posted - 2010-11-15 : 16:20:30
|
| I have a table of attorny's. Each Attorney has a CaseNumber, CaseParty, NameID. NameID = Name of the attorney, Caseparty = Name of person that NameID is representing, and the CaseNumber = Identification of the case at hand. My table Looks Similar to this:CaseNumber```````````CaseParty`````````````````NameID``````````````````TitleID00CAS00003```````MC COY, CRAIGARY`````NORMAN P SOLZE, ESQ.``````````PLAINTIFF00CAS00003```````OHIO`````````````````RONALD J MAYLE, ESQ.``````````DEFENDANT00CAS00003```````OHIO`````````````````SIXTH DISTRICT````````````````DEFENDANTI want to select the first CaseNumber, Plaintiff, Plaintiff Attorney, Defendant, Defendant Attorney all within one row. That would mean that SIXTH DISTRICT row would not be included and my final view would look like this:CaseNumber``````Plaintiff````````````PlaintiffAttorney````````Defendant`````DefendantAttorney00CAS00003```MC COY, CRAIGARY```NORMAN P SOLZE, ESQ.````OHIO```````RONALD J MAYLE, ESQ.How would I go about performing this? Any input is well appreciated!! |
|
|
Lamprey
Master Smack Fu Yak Hacker
4614 Posts |
Posted - 2010-11-15 : 17:04:48
|
| Is there any logic to determine which row (if any) should be excluded? In this example the NameID of "Sixth DIstrict" would be exclued, but is that the only way to identify that that tuple? |
 |
|
|
esilva002
Starting Member
10 Posts |
Posted - 2010-11-16 : 10:24:14
|
quote:
Originally posted by Lamprey
Is there any logic to determine which row (if any) should be excluded? In this example the NameID of "Sixth DIstrict" would be exclued, but is that the only way to identify that that tuple?
Truly, I only want the first defendant and the first plaintiff that would show up on the table. The NameID of "Sixth District" would not appear because it does not show up first in the table. I believe I could use code such as:ROW_NUMBER() OVER (PARTITION BY CaseNumber ORDER BY TitleID) AS rn But I dont know where, I need direction, just a little lost. |
|
|
|
Lamprey
Master Smack Fu Yak Hacker
4614 Posts |
Posted - 2010-11-16 : 11:55:35
|
| There is no concept of FIRST in a database without an ORDER BY. So, unfortunately, your assumption is wrong. Thus, doing an ORDER BY TitleID and no other column will get a random row for TitleIDs with multiple rows.Are there other columns that you could use to determine order? Is there another column that indicates if the NameID is a person versus a court or even another "lookup" table that has a list of Courts or something? If not, then you might be working with a flawed schema. |
|
|
|
esilva002
Starting Member
10 Posts |
Posted - 2010-11-16 : 14:37:51
|
| your correct but i have other options than to use TitleID, actually i can use NameID, better but i guess i didn't need a forum to tell me that. Too bad no one has had this happen before |
|
|
|
|
|
|
|
|
View Issues