Home » SQL & PL/SQL » SQL & PL/SQL » Getting one line from table per person
Getting one line from table per person [message #436450] |
Wed, 23 December 2009 11:56  |
Anne Simms
Messages: 163 Registered: May 2002 Location: San Francisco, CA
|
Senior Member |
|
|
I have a table that has the following columns:
SPRING
(
SPRING_ID NUMBER(8) NOT NULL,
SPRING_PRIORITY VARCHAR2(1 BYTE) NOT NULL,
SPRING_LAST_NAME VARCHAR2(25 BYTE) NOT NULL,
SPRING_FIRST_NAME VARCHAR2(15 BYTE) NOT NULL,
SPRING_MI VARCHAR2(1 BYTE),
SPRING_STREET_LINE1 VARCHAR2(30 BYTE),
SPRING_STREET_LINE2 VARCHAR2(30 BYTE),
SPRING_STREET_LINE3 VARCHAR2(30 BYTE),
SPRING_CITY VARCHAR2(20 BYTE),
SPRING_STAT_CODE VARCHAR2(3 BYTE),
SPRING_NATN_CODE VARCHAR2(5 BYTE),
SPRING_ZIP VARCHAR2(10 BYTE),
SPRING_PHONE_AREA VARCHAR2(3 BYTE),
SPRING_PHONE_NUMBER VARCHAR2(7 BYTE),
SPRING_PHONE_EXT VARCHAR2(4 BYTE),
SPRING_REC_CODE VARCHAR2(1 BYTE),
SPRING_ACTIVITY_DATE DATE NOT NULL,
SPRING_ATYP_CODE VARCHAR2(2 BYTE),
SPRING_DATA_ORIGIN VARCHAR2(30 BYTE),
SPRING_USER_ID VARCHAR2(30 BYTE)
)
I'm only using the following columns in my query:
[code]
SPRING_ID
SPRING_LAST_NAME
SPRING_FIRST_NAME
SPRING_MI
SPRING_STREET_LINE1
SPRING_STREET_LINE2
SPRING_STREET_LINE3
SPRING_CITY
SPRING_ZIP
SPRING_PHONE_AREA
SPRING_PHONE_NUMBER
SPRING_REC_CODE
[/code]
When I do a:
[code]
select Distinct column1, column2, column3, etc
from spring;
I get 2 to 3 rows of data for one person for example below:
pam, alias, 111-234-4565, blank address, blank city, blank zip
pam, alias, 112-235-5698, 123 pop street,new mexico, 11123
[/code]
How can I get one row of data per person without having to put a bunch of conditions like adding a lot of where clauses?
Anne
|
|
|
|
|
|
|
Re: Getting one line from table per person [message #436473 is a reply to message #436453] |
Wed, 23 December 2009 14:07   |
Anne Simms
Messages: 163 Registered: May 2002 Location: San Francisco, CA
|
Senior Member |
|
|
I was thinking maybe I can do a Max on the activity_data within my query, which I think this will give me the most current data..correct?
SPRING
(
SPRING_ID NUMBER(8) NOT NULL,
SPRING_PRIORITY VARCHAR2(1 BYTE) NOT NULL,
SPRING_LAST_NAME VARCHAR2(25 BYTE) NOT NULL,
SPRING_FIRST_NAME VARCHAR2(15 BYTE) NOT NULL,
SPRING_MI VARCHAR2(1 BYTE),
SPRING_STREET_LINE1 VARCHAR2(30 BYTE),
SPRING_STREET_LINE2 VARCHAR2(30 BYTE),
SPRING_STREET_LINE3 VARCHAR2(30 BYTE),
SPRING_CITY VARCHAR2(20 BYTE),
SPRING_STAT_CODE VARCHAR2(3 BYTE),
SPRING_NATN_CODE VARCHAR2(5 BYTE),
SPRING_ZIP VARCHAR2(10 BYTE),
SPRING_PHONE_AREA VARCHAR2(3 BYTE),
SPRING_PHONE_NUMBER VARCHAR2(7 BYTE),
SPRING_PHONE_EXT VARCHAR2(4 BYTE),
SPRING_REC_CODE VARCHAR2(1 BYTE),
SPRING_ACTIVITY_DATE DATE NOT NULL,
SPRING_ATYP_CODE VARCHAR2(2 BYTE),
SPRING_DATA_ORIGIN VARCHAR2(30 BYTE),
SPRING_USER_ID VARCHAR2(30 BYTE)
)
|
|
|
|
Re: Getting one line from table per person [message #436476 is a reply to message #436474] |
Wed, 23 December 2009 14:25   |
Anne Simms
Messages: 163 Registered: May 2002 Location: San Francisco, CA
|
Senior Member |
|
|
spring_activity_date,spring_atyp_code, spring_first_name,spring_last_name,spring_natin_code, spring_id, spring_priority,spring_relt_code and spring_stat_code are constraints on this spring table.
And all those columns can't be null.
Anne
|
|
|
|
Goto Forum:
Current Time: Tue Feb 18 10:03:38 CST 2025
|