Home » SQL & PL/SQL » SQL & PL/SQL » Help with top query (Oracle 11g,Windows 2003, Toad for Oracle 11)
icon5.gif  Help with top query [message #580300] Fri, 22 March 2013 10:03 Go to next message
jxh461
Messages: 177
Registered: March 2005
Senior Member
I have a table Trans_by_date setup as follows:
My select statement is :
select dis,cit,area,add_date,location,transaction_type,order_type,nam,transaction_date
from table Trans_by_date;



DIS	CIT	AREA	ADD_DATE	        LOCATIO		TRANS		O	NAM	TRANSACTION_DATE
----	-----	-----	---------	-------		------		--	---	---------------------
AA	233	3177	6/21/1967	Atlanta	  	Update		Q	jxh	10/12/2012 2:26:20 PM
AA	233	3177	6/21/1967	Atlanta	  	Update		Q	jxh	10/12/2012 3:13:04 PM
AA	233	3027	8/14/2012	Atlanta	  	Update		T	jxh	10/12/2012 4:13:04 PM
AA	233	3027	8/14/2012	Atlanta	  	Update		T	jxh	10/12/2012 8:22:14 PM
AA	233	6848	1/07/1998	Atlanta	  	Update		A	jxh	10/12/2012 3:13:04 PM
AA	233	8059	3/28/2005	Atlanta	  	Update		B	jxh	10/12/2012 3:13:04 PM
AA	233	1259	5/05/2011	Atlanta	  	Update		C	jxh	10/12/2012 3:13:04 PM
AA	233	1084	7/10/2012	Atlanta	  	Update		D	jxh	10/12/2012 3:13:04 PM


In the case where the records match every column except for the transaction date (ex: row 1&2 or 3&4)
I would like to return the record with the latest date, as follows:

DIS	CIT	AREA	ADD_DATE	        LOCATIO		TRANS		O	NAM	TRANSACTION_DATE
----	-----	-----	---------	-------		------		--	---	---------------------

AA	233	3177	6/21/1967	Atlanta	  	Update		Q	jxh	10/12/2012 3:13:04 PM

AA	233	3027	8/14/2012	Atlanta	  	Update		T	jxh	10/12/2012 8:22:14 PM
AA	233	6848	1/07/1998	Atlanta	  	Update		A	jxh	10/12/2012 3:13:04 PM
AA	233	8059	3/28/2005	Atlanta	  	Update		B	jxh	10/12/2012 3:13:04 PM
AA	233	1259	5/05/2011	Atlanta	  	Update		C	jxh	10/12/2012 3:13:04 PM
AA	233	1084	7/10/2012	Atlanta	  	Update		D	jxh	10/12/2012 3:13:04 PM



--The query will obviously return everything. How can I use top row query to return what I want ?
Re: Help with top query [message #580301 is a reply to message #580300] Fri, 22 March 2013 10:08 Go to previous messageGo to next message
BlackSwan
Messages: 22839
Registered: January 2009
Senior Member
>My select statement is :
I don't believe you.
>select dis,cit,area,add_date,location,transaction_type,order_type,nam,transaction_date from table Trans_by_date;
above is NOT valid syntax due to malformed FROM clause.

you would benefit by using MAX() function.
Re: Help with top query [message #580302 is a reply to message #580301] Fri, 22 March 2013 10:28 Go to previous messageGo to next message
jxh461
Messages: 177
Registered: March 2005
Senior Member
CORRECTED SYNTAX: Beleive me now ?
select dis,cit,area,add_date,location,transaction_type,order_type,nam,transaction_date
from Trans_by_date
Re: Help with top query [message #580303 is a reply to message #580302] Fri, 22 March 2013 10:38 Go to previous messageGo to next message
Michel Cadot
Messages: 59279
Registered: March 2007
Location: Nanterre, France, http://...
Senior Member
Account Moderator
Have a look at RANK function.

Note if it is possible that all columns but one are the same ones then your model is wrong.
Read Normalization.

Regards
Michel

[Updated on: Fri, 22 March 2013 10:38]

Report message to a moderator

Re: Help with top query [message #580308 is a reply to message #580303] Fri, 22 March 2013 11:44 Go to previous messageGo to next message
jxh461
Messages: 177
Registered: March 2005
Senior Member
The scenario that I am trying to account for is if an entry is made in the table more than once in a day.
Basically what is happening is that on the front end of the application the record looks like a duplicate because the transaction_date is truncated. In this case I want the latest record to be pulled not both.

I am playing around with the max function. I will also look into the rank function if needed.
Re: Help with top query [message #580310 is a reply to message #580302] Fri, 22 March 2013 12:15 Go to previous messageGo to next message
John Watson
Messages: 4610
Registered: January 2010
Location: Global Village
Senior Member
select dis,cit,area,add_date,location,transaction_type,order_type,nam,max(transaction_date)
from Trans_by_date
group by dis,cit,area,add_date,location,transaction_type,order_type,nam;
Re: Help with top query [message #581922 is a reply to message #580310] Thu, 11 April 2013 17:35 Go to previous message
jxh461
Messages: 177
Registered: March 2005
Senior Member
My solution:

select dis,cit,area,add_date,location,transaction_type,order_type,nam,trunc(max(transaction_date))
from Trans_by_date
group by dis,cit,area,add_date,location,transaction_type,order_type,nam;

Very similar to Mr. Watson's above. Thanks.
Previous Topic: Rows not in Inner Select
Next Topic: REGEXP_LIKE does not recognize [^@] as a string without a '@' in it
Goto Forum:
  


Current Time: Tue Sep 30 18:13:39 CDT 2014

Total time taken to generate the page: 0.09137 seconds