Return only 1 row - SQL Server Forums

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.

All Forums

General SQL Server Forums

New to SQL Server Programming

Return only 1 row

Author

Topic

palbar
Starting Member

8 Posts

Posted - 2012-04-12 : 12:08:42

When I run the following query:

SELECT FMR_TABLE_ID_NO, FMR.TABLE_FIRST_NAME||' '||FMR.TABLE_MI||' '||FMR.TABLE_LAST_NAME AS FORMER_NAME,
ROW_NUMBER() OVER (PARTITION BY FMR.TABLE_ID_NO ORDER BY FMR.TABLE_ACTIVITY_DATE) AS RN
FROM TABLE FMR
WHERE FMR.TABLE_CHANGE_IND IS NOT NULL
AND FMR.TABLE_NTYP_CODE = 'FRMR'

I get:
FMR_TABLE_ID_NO FORMER_NAME RN
11111 Danny Test 1
22222 Amanda Gomez 1
33333 Cheryl Campbell 1
33333 Cheryl Mitchell 2
44444 Cindy Joy 1
55555 Carol Kean 1
55555 Carol Cashman 2
66666 Dennis Sanders 1
77777 Albert Eden 1

What I'm trying to accomplish is to return only those rows that equal 1. I've been searching on a way to do it but I haven't any luck, could somebody please give me a hand, unfortunately the FMR.TABLE_ACTIVITY_DATE are the same for those with more than 1 row. This will be used in a view I'm writing.

ahmeds08
Aged Yak Warrior

737 Posts

Posted - 2012-04-12 : 12:19:56

try adding ROWNUM=1 in your where clause.

visakh16
Very Important crosS Applying yaK Herder

52326 Posts

Posted - 2012-04-12 : 17:43:44

[code]
SELECT columns...
FROM
(
SELECT FMR_TABLE_ID_NO, FMR.TABLE_FIRST_NAME||' '||FMR.TABLE_MI||' '||FMR.TABLE_LAST_NAME AS FORMER_NAME,
ROW_NUMBER() OVER (PARTITION BY FMR.TABLE_ID_NO ORDER BY FMR.TABLE_ACTIVITY_DATE) AS RN
FROM TABLE FMR
WHERE FMR.TABLE_CHANGE_IND IS NOT NULL
AND FMR.TABLE_NTYP_CODE = 'FRMR'
)t
WHERE RN=1
[/code]

------------------------------------------------------------------------------------------------------
SQL Server MVP
http://visakhm.blogspot.com/

palbar
Starting Member

8 Posts

Posted - 2012-04-12 : 19:30:00

The "columns..." I want is the concatenation of the first_name, mi and last_name (just one column). When I put FMR.TABLE_FIRST_NAME||' '||:FMR.TABLE_MI||' '||FMR.TABLE_LAST_NAME where columns..." is, I get an error that says: ORA-00904:"FMR"."TABLE_LAST_NAME":invalid identifier 00904.00000 - "%s:invalid identifier"


SELECT FMR.TABLE_FIRST_NAME||' '||FMR.TABLE_MI||' '||FMR.TABLE_LAST_NAME AS FORMER_NAME
FROM
(
SELECT FMR.TABLE_FIRST_NAME||' '||FMR.TABLE_MI||' '||FMR.TABLE_LAST_NAME AS FORMER_NAME,ROW_NUMBER() OVER (PARTITION BY FMR.TABLE_PIDM ORDER BY FMR.TABLE_ACTIVITY_DATE) AS RN
FROM TABLE FMR
WHERE FMR.TABLE_CHANGE_IND IS NOT NULL
AND FMR.TABLE_NTYP_CODE = 'FRMR'
) T
WHERE RN = 1

vinu.vijayan
Posting Yak Master

227 Posts

Posted - 2012-04-13 : 01:25:49

Try This:


SELECT FMR_TABLE_ID_NO, FMR.TABLE_FIRST_NAME||' '||FMR.TABLE_MI||' '||FMR.TABLE_LAST_NAME AS FORMER_NAME,
ROW_NUMBER() OVER (PARTITION BY FMR.TABLE_ID_NO ORDER BY FMR.TABLE_ACTIVITY_DATE) AS RN
FROM TABLE FMR
WHERE FMR.TABLE_CHANGE_IND IS NOT NULL
AND FMR.TABLE_NTYP_CODE = 'FRMR' AND RN = 1

Vinu Vijayan

N 28° 33' 11.93148"
E 77° 14' 33.66384"

palbar
Starting Member

8 Posts

Posted - 2012-04-13 : 09:29:27

[code]
SELECT FORMER_NAME
FROM
(
SELECT FMR.TABLE_FIRST_NAME||' '||FMR.TABLE_MI||' '||FMR.TABLE_LAST_NAME AS FORMER_NAME,
ROW_NUMBER() OVER (PARTITION BY FMR.TABLE_PIDM ORDER BY FMR.TABLE_ACTIVITY_DATE) AS RN
FROM TABLE FMR
WHERE FMR.TABLE_CHANGE_IND IS NOT NULL
AND FMR.TABLE_NTYP_CODE = 'FRMR'
) T
WHERE ROWNUM = 1
[/code]
I've written the above code and it only returns the first row:
Danny Test

where it should return:
Danny Test
Amanda Gomez
Cheryl Campbell
Cindy Joy
Carol Kean
Dennis Sanders
Albert Eden

I just need the former name and if the person has more than 1 former name I just want the 1st row.
eg. from the above example
Cheryl Campbell and Cheryl Mitchell are the same person but I want Cheryl Campbell because her "RN" is 1;

Carol Kean and Carol Cashman are the same person but I want
Carol Kean because her "RN" is 1.

palbar
Starting Member

8 Posts

Posted - 2012-04-13 : 09:34:37

quote:

Originally posted by vinu.vijayan

Try This:


SELECT FMR_TABLE_ID_NO, FMR.TABLE_FIRST_NAME||' '||FMR.TABLE_MI||' '||FMR.TABLE_LAST_NAME AS FORMER_NAME,
ROW_NUMBER() OVER (PARTITION BY FMR.TABLE_ID_NO ORDER BY FMR.TABLE_ACTIVITY_DATE) AS RN
FROM TABLE FMR
WHERE FMR.TABLE_CHANGE_IND IS NOT NULL
AND FMR.TABLE_NTYP_CODE = 'FRMR' AND RN = 1

Vinu Vijayan

N 28° 33' 11.93148"
E 77° 14' 33.66384"

quote:

Gave me an error:
0RA-00904: "RN": invalid identifier
00904.00000 - "%s:invalid identifier"

visakh16
Very Important crosS Applying yaK Herder

52326 Posts

Posted - 2012-04-13 : 11:10:32

you should have told this before.
Ther error message clearly suggest you're using Oracle
0RA-00904: "RN": invalid identifier

This is MS SQL SERver forum and solutions given here are mostly sql server specific. So please post in some oracle forum like www.orafaq.com if you need Oracle specific help

------------------------------------------------------------------------------------------------------
SQL Server MVP
http://visakhm.blogspot.com/

palbar
Starting Member

8 Posts

Posted - 2012-04-13 : 11:19:31

quote:
Originally posted by visakh16

you should have told this before.
Ther error message clearly suggest you're using Oracle
0RA-00904: "RN": invalid identifier

This is MS SQL SERver forum and solutions given here are mostly sql server specific. So please post in some oracle forum like www.orafaq.com if you need Oracle specific help

------------------------------------------------------------------------------------------------------
SQL Server MVP
http://visakhm.blogspot.com/

So sorry!!!

visakh16
Very Important crosS Applying yaK Herder

52326 Posts

Posted - 2012-04-13 : 11:33:25

quote:

Originally posted by vinu.vijayan

Try This:


SELECT FMR_TABLE_ID_NO, FMR.TABLE_FIRST_NAME||' '||FMR.TABLE_MI||' '||FMR.TABLE_LAST_NAME AS FORMER_NAME,
ROW_NUMBER() OVER (PARTITION BY FMR.TABLE_ID_NO ORDER BY FMR.TABLE_ACTIVITY_DATE) AS RN
FROM TABLE FMR
WHERE FMR.TABLE_CHANGE_IND IS NOT NULL
AND FMR.TABLE_NTYP_CODE = 'FRMR' AND RN = 1

Vinu Vijayan

N 28° 33' 11.93148"
E 77° 14' 33.66384"

Did you test this?
I dont think it will work
Please test your code before posting

------------------------------------------------------------------------------------------------------
SQL Server MVP
http://visakhm.blogspot.com/

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources