June 7, 2011 at 9:48 am
ACCT_IDACCT_NOACCT_NAMEACCT_ROW
111ABC 100
111ABC 101
212XYZ 102
212XYZ 103
I WANT A OUTPUT LIKE
111ABC 101
212XYZ 103
BUT I DON;T WANT OUTPUT BASE ON ACCT_ROW.(I DO NOW WANT TO USE MIN OR MAX)
I WANT A OUTPUT BASED ON LATEST ACCT_ID.
I WANT A OUTPUT BASED ON LAST ACCT
_ID(UPDATED).
Thanks
l
June 7, 2011 at 9:55 am
The Acct_ID column is identical within the Acct_No value, but you say don't use the only value that actually does differentiate the rows. Your requirements, as stated, are different than your sample output. Which one is correct?
- Gus "GSquared", RSVP, OODA, MAP, NMVP, FAQ, SAT, SQL, DNA, RNA, UOI, IOU, AM, PM, AD, BC, BCE, USA, UN, CF, ROFL, LOL, ETC
Property of The Thread
"Nobody knows the age of the human race, but everyone agrees it's old enough to know better." - Anon
June 7, 2011 at 10:50 am
I want to make acc_id as primary key so i need to remove duplicate...
as per my rek,,,
I want only latest one( last row)
like ..
acct_id
1--1st row
1--2nd row
and I want a output
1--2nd row.
Thanks
L
June 7, 2011 at 11:19 am
The only difference I see between the rows is column Acct_Row, but your post says, "BUT I DON;T WANT OUTPUT BASE ON ACCT_ROW".
So, what do you want the output based on?
- Gus "GSquared", RSVP, OODA, MAP, NMVP, FAQ, SAT, SQL, DNA, RNA, UOI, IOU, AM, PM, AD, BC, BCE, USA, UN, CF, ROFL, LOL, ETC
Property of The Thread
"Nobody knows the age of the human race, but everyone agrees it's old enough to know better." - Anon
June 7, 2011 at 1:31 pm
I want my output based on acct_id
June 7, 2011 at 1:55 pm
patla4u (6/7/2011)
I want my output based on acct_id
But the account ID is the same (two records have 1, two records have 2).
You have two rows with an account ID of 1. Are you saying it doesn't matter which of those two rows the query picks, just so long as it only picks one of them?
- Gus "GSquared", RSVP, OODA, MAP, NMVP, FAQ, SAT, SQL, DNA, RNA, UOI, IOU, AM, PM, AD, BC, BCE, USA, UN, CF, ROFL, LOL, ETC
Property of The Thread
"Nobody knows the age of the human race, but everyone agrees it's old enough to know better." - Anon
June 7, 2011 at 2:36 pm
ACCT_ID ACCT_NO ACCT_NAME ACCT_ROW
1 11 ABC 100.......................................(yesterday)
1 11 ABC 101.......................................(today)
2 12 XYZ 102.......................................(yesterday)
2 12 XYZ 103.......................................(today)
I don't want pick acct_id rendom.
I want acc_id updated one.
like ouput
1 11 ABC 101.......................................(today)
2 12 XYZ 103.......................................(today)
I give you one more example,
June 7, 2011 at 2:44 pm
You want this based on Acct_ID and acct_row then. When you specify the ordering, you are choosing this based on some other value. There is no ordering in SQL Server, and the server doesn't know which row was today and which was yesterday unless you have another field that determines this.
June 7, 2011 at 2:50 pm
Can i know how to add extra field and how can i identify today_acct_id.
Thank
L
June 7, 2011 at 2:56 pm
You are saying that sql server generate a query randomly
like
ACCT_ID ACCT_NO ACCT_NAME ACCT_ROW
1 11 ABC 100.......................................(yesterday)
1 11 ABC 101.......................................(today)
2 12 XYZ 102.......................................(yesterday)
2 12 XYZ 103.......................................(today)
or
ACCT_ID ACCT_NO ACCT_NAME ACCT_ROW
1 11 ABC 101.......................................(today)
1 11 ABC 100.......................................(yesterday)
2 12 XYZ 103.......................................(today)
2 12 XYZ 102.......................................(yesterday)
and I can not know that...which is is latest one....right???
June 7, 2011 at 3:28 pm
SQL Server doesn't track when something was inserted. There is no ordering to records, meaning that if you inserted
1 11 ABC 100
1 11 ABC 101
2 12 XYZ 102
2 12 XYZ 103
or
2 12 XYZ 102
1 11 ABC 101
2 12 XYZ 103
1 11 ABC 100
The same ordering could be returned in a query without an ORDER BY. If you are assuming that rows are being stored in the order they are inserted, you are making a huge mistake.
You need to provide some type of ordering field in order to track when things are inserted. If the acct_row is incrementing, then you can potentially determine which is the "latest" record.
June 8, 2011 at 6:38 am
patla4u (6/7/2011)
You are saying that sql server generate a query randomlylike
ACCT_ID ACCT_NO ACCT_NAME ACCT_ROW
1 11 ABC 100.......................................(yesterday)
1 11 ABC 101.......................................(today)
2 12 XYZ 102.......................................(yesterday)
2 12 XYZ 103.......................................(today)
or
ACCT_ID ACCT_NO ACCT_NAME ACCT_ROW
1 11 ABC 101.......................................(today)
1 11 ABC 100.......................................(yesterday)
2 12 XYZ 103.......................................(today)
2 12 XYZ 102.......................................(yesterday)
and I can not know that...which is is latest one....right???
Exactly correct.
Without a column indicating the sequence or the date/time, there is no way to tell which was first, which was last, etc. The computer only has the data you give it. If you can't tell it which row is which, it can't "figure it out" for you. Computers don't figure things out, they do what you tell them.
- Gus "GSquared", RSVP, OODA, MAP, NMVP, FAQ, SAT, SQL, DNA, RNA, UOI, IOU, AM, PM, AD, BC, BCE, USA, UN, CF, ROFL, LOL, ETC
Property of The Thread
"Nobody knows the age of the human race, but everyone agrees it's old enough to know better." - Anon
Viewing 12 posts - 1 through 11 (of 11 total)
You must be logged in to reply to this topic. Login to reply