Home » SQL & PL/SQL » SQL & PL/SQL » How to get the first transaction for every unique client?
How to get the first transaction for every unique client? [message #481299] Tue, 02 November 2010 06:35 Go to next message
pyscho
Messages: 134
Registered: December 2009
Senior Member
Hi guys, I have a table called pf_stock_txns which just stores all of a clients transactions

If I do select distinct(client_id) from pf_stock_txns then I get back a unqiue list of all clients.

However, I need a query that will give me the first transaction for every unqiue client. any ideas guys?

Thanks

Report message to a moderator

Re: How to get the first transaction for every unique client? [message #481301 is a reply to message #481299] Tue, 02 November 2010 06:41 Go to previous messageGo to next message
cookiemonster
Messages: 13925
Registered: September 2008
Location: Rainy Manchester
Senior Member
Possibly using the row_number analytic function.

Since we have no idea how you identify the first transaction in your data it's hard to say for sure.

Report message to a moderator

Re: How to get the first transaction for every unique client? [message #481302 is a reply to message #481301] Tue, 02 November 2010 06:48 Go to previous messageGo to next message
pyscho
Messages: 134
Registered: December 2009
Senior Member
Well to get the first transaction for client 101 i would just do this

select * from pf_stock_txns where client_id='101' and rownum=1


Could you show me how I could use row num to get the first transaction for every unique client?

Report message to a moderator

Re: How to get the first transaction for every unique client? [message #481303 is a reply to message #481302] Tue, 02 November 2010 06:52 Go to previous messageGo to next message
Roachcoach
Messages: 1576
Registered: May 2010
Location: UK
Senior Member
That will give you a random transaction, you can use rownum like that.

I suspect, and the other folks here will be able to correct me if I'm wrong, all that does is return the first row it finds in the buffer where the ID is 101. As to what is in the buffer and in what order...you've no control over.

Report message to a moderator

Re: How to get the first transaction for every unique client? [message #481304 is a reply to message #481303] Tue, 02 November 2010 06:53 Go to previous messageGo to next message
Roachcoach
Messages: 1576
Registered: May 2010
Location: UK
Senior Member
Aw no edit any more. bah.

I meant you CANTuse rownum like that

Report message to a moderator

Re: How to get the first transaction for every unique client? [message #481306 is a reply to message #481304] Tue, 02 November 2010 06:57 Go to previous messageGo to next message
cookiemonster
Messages: 13925
Registered: September 2008
Location: Rainy Manchester
Senior Member
As Roachcoach says that query just gives you a random row with that client_id. Is that really what you want?

Report message to a moderator

Re: How to get the first transaction for every unique client? [message #481309 is a reply to message #481306] Tue, 02 November 2010 07:24 Go to previous messageGo to next message
pyscho
Messages: 134
Registered: December 2009
Senior Member
Well yep. I just want to get a (one random one) transaction for every unique clients

Report message to a moderator

Re: How to get the first transaction for every unique client? [message #481311 is a reply to message #481309] Tue, 02 November 2010 07:32 Go to previous messageGo to next message
ayush_anand
Messages: 417
Registered: November 2008
Senior Member
 
SELECT *
  FROM (SELECT st.*, ROW_NUMBER () OVER (PARTITION BY client_id ORDER BY 1) rn
          FROM pf_stock_txns st)
 WHERE rn = 1

Report message to a moderator

Re: How to get the first transaction for every unique client? [message #481312 is a reply to message #481309] Tue, 02 November 2010 07:32 Go to previous messageGo to next message
delna.sexy
Messages: 941
Registered: December 2008
Location: Surat, The Diamond City
Senior Member
You can use analytical functions available for this purpose
like row_number, first_value
Search Oracle Documentation for Analytic functions.

regards,
Delna

Report message to a moderator

Re: How to get the first transaction for every unique client? [message #481313 is a reply to message #481311] Tue, 02 November 2010 07:33 Go to previous messageGo to next message
ayush_anand
Messages: 417
Registered: November 2008
Senior Member
Sorry

SELECT *
  FROM (SELECT st.*, ROW_NUMBER () rn OVER (PARTITION BY client_id ORDER BY 1) 
          FROM pf_stock_txns st)
 WHERE rn = 1

Report message to a moderator

Re: How to get the first transaction for every unique client? [message #481314 is a reply to message #481313] Tue, 02 November 2010 07:36 Go to previous messageGo to next message
delna.sexy
Messages: 941
Registered: December 2008
Location: Surat, The Diamond City
Senior Member
I have not run your query but I think, previous query was the correct one.

regards,
Delna

Report message to a moderator

Re: How to get the first transaction for every unique client? [message #481315 is a reply to message #481313] Tue, 02 November 2010 07:37 Go to previous messageGo to next message
Littlefoot
Messages: 21811
Registered: June 2005
Location: Croatia, Europe
Senior Member
Account Moderator
Yes, you should be sorry (but only for the second attempt, the first one was OK).

Report message to a moderator

Re: How to get the first transaction for every unique client? [message #481316 is a reply to message #481315] Tue, 02 November 2010 07:38 Go to previous messageGo to next message
ayush_anand
Messages: 417
Registered: November 2008
Senior Member
Sorry gone out of my mind Sad

Report message to a moderator

Re: How to get the first transaction for every unique client? [message #481317 is a reply to message #481314] Tue, 02 November 2010 07:38 Go to previous messageGo to next message
pyscho
Messages: 134
Registered: December 2009
Senior Member
Hi ayush, I get the error with your last query

missing window specification for this function

Cause: All window functions should be followed by window specification, like <function>(<argument list>) OVER (<window specification>)

Action: None

Many thanks.

Report message to a moderator

Re: How to get the first transaction for every unique client? [message #481318 is a reply to message #481317] Tue, 02 November 2010 07:39 Go to previous messageGo to next message
ayush_anand
Messages: 417
Registered: November 2008
Senior Member
 
SELECT *
  FROM (SELECT st.*, ROW_NUMBER () OVER (PARTITION BY client_id ORDER BY 1 rn) 
          FROM pf_stock_txns st)
 WHERE rn = 1

Report message to a moderator

Re: How to get the first transaction for every unique client? [message #481319 is a reply to message #481318] Tue, 02 November 2010 07:41 Go to previous messageGo to next message
ayush_anand
Messages: 417
Registered: November 2008
Senior Member
Sorry quitting for day 

SELECT *
  FROM (SELECT st.*, ROW_NUMBER () OVER (PARTITION BY client_id ORDER BY 1) rn 
          FROM pf_stock_txns st)
 WHERE rn = 1


somebody please add edit button

Report message to a moderator

Re: How to get the first transaction for every unique client? [message #481321 is a reply to message #481319] Tue, 02 November 2010 07:45 Go to previous messageGo to next message
pyscho
Messages: 134
Registered: December 2009
Senior Member
perfect, many thanks.

Report message to a moderator

Re: How to get the first transaction for every unique client? [message #481327 is a reply to message #481321] Tue, 02 November 2010 08:24 Go to previous message
cookiemonster
Messages: 13925
Registered: September 2008
Location: Rainy Manchester
Senior Member
Random row != first transaction
It'd save a fair amount of time if you were clearer in stating your requirements.

Report message to a moderator

Previous Topic: full outer joins, code works but don't understand
Next Topic: LEFT ANSI JOIN Not Working Properly b/w Oracle Versions
Goto Forum:
  

[ Syndicate this forum (XML) ] [ RSS ]

Current Time: Wed Jun 26 06:04:53 CDT 2024
.:: Forum Home :: Blogger Home :: Wiki Home :: Contact :: Privacy ::.