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 Go to next message
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 #436451 is a reply to message #436450] Wed, 23 December 2009 11:58 Go to previous messageGo to next message
BlackSwan
Messages: 24915
Registered: January 2009
Senior Member
>How can I get one row of data per person
which row or exactly what should the desired row look like?
Re: Getting one line from table per person [message #436453 is a reply to message #436450] Wed, 23 December 2009 12:14 Go to previous messageGo to next message
ThomasG
Messages: 3186
Registered: April 2005
Location: Heilbronn, Germany
Senior Member
group by
Re: Getting one line from table per person [message #436457 is a reply to message #436451] Wed, 23 December 2009 12:32 Go to previous messageGo to next message
Anne Simms
Messages: 163
Registered: May 2002
Location: San Francisco, CA
Senior Member
Basically, I want to get the most current data that was inserted into the table.


Anne
Re: Getting one line from table per person [message #436458 is a reply to message #436457] Wed, 23 December 2009 12:35 Go to previous messageGo to next message
BlackSwan
Messages: 24915
Registered: January 2009
Senior Member
It would be helpful if you provided DDL for tables involved.
It would be helpful if you provided DML for test data.
It would be helpful if you provided expected/desired results & a detailed explanation how & why the test data gets transformed or organized.

>Basically, I want to get the most current data that was inserted into the table.

Programatically how to identify "most current data"?
Re: Getting one line from table per person [message #436473 is a reply to message #436453] Wed, 23 December 2009 14:07 Go to previous messageGo to next message
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 #436474 is a reply to message #436473] Wed, 23 December 2009 14:11 Go to previous messageGo to next message
BlackSwan
Messages: 24915
Registered: January 2009
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?
"activity_data"?
perhaps
ACTIVITY_DATE?

This is your application; not ours.
We have no idea when or what gets placed in this field.

What happens if/when two rows have same ACTIITY_DATE?
What prevents duplicate ACTIVITY_DATE in a multi-user environment?
Re: Getting one line from table per person [message #436476 is a reply to message #436474] Wed, 23 December 2009 14:25 Go to previous messageGo to next message
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
Re: Getting one line from table per person [message #436477 is a reply to message #436476] Wed, 23 December 2009 14:52 Go to previous message
ThomasG
Messages: 3186
Registered: April 2005
Location: Heilbronn, Germany
Senior Member
rank
Previous Topic: delete with trigger (merged many same question)
Next Topic: What is the best (most efficient) way to process complex Select Queries?
Goto Forum:
  


Current Time: Fri Sep 30 19:18:37 CDT 2016

Total time taken to generate the page: 0.44756 seconds