Home » SQL & PL/SQL » SQL & PL/SQL » SQL syntax
SQL syntax [message #202892] Mon, 13 November 2006 03:46 Go to next message
juicyapple
Messages: 92
Registered: October 2005
Member
Hi,
I have create a table MachineSetup with column
SetupURN INTEGER
MachineNo VARCHAR2(10)
StartTime DATETIME
EndTime DATETIME

where SetupURN is the primary key of the table.

If the table has record
SetupURN MachineNo StartTime EndTime
-------- --------- --------- --------
1 A1 2006-10-17 2006-10-18
2 A1 2006-10-19 2006-10-20
3 A2 2006-10-16 2006-10-20

I want to select all Machine No but for duplicate Machine No, I want only Machine No which has the latest date of EndTime.

Can anyone show me the query??




Re: SQL syntax [message #202895 is a reply to message #202892] Mon, 13 November 2006 04:07 Go to previous messageGo to next message
vjeedigunta
Messages: 201
Registered: March 2005
Location: Hyderabad
Senior Member

try this .. (its been a while since i wrote typical sql Smile )

Quote:
select * from table_name a
where trunc(date_colum)= (select trunc(max(date_column) from table b where a.machineid=b.machineid);


Hope that helps
-Sai Jeedigunta
http://vasujeedigunta.blogspot.com
Re: SQL syntax [message #203198 is a reply to message #202892] Tue, 14 November 2006 05:14 Go to previous messageGo to next message
pravin3032
Messages: 51
Registered: November 2006
Location: eARTH
Member
hi

try this quey,

SELECT MACHINENO,
MAX (ENDTIME)
FROM MACHINESETUP
GROUP BY MACHINENO
HAVING COUNT (MACHINENO) > 1;

Re: SQL syntax [message #203340 is a reply to message #203198] Tue, 14 November 2006 18:36 Go to previous messageGo to next message
juicyapple
Messages: 92
Registered: October 2005
Member
Thanks for your reply.

If I want to filter out machine no. which current date is between start time and end time?
Re: SQL syntax [message #203387 is a reply to message #203340] Wed, 15 November 2006 00:10 Go to previous message
a_developer
Messages: 194
Registered: January 2006
Senior Member
SELECT MACHINENO
FROM MACHINESETUP
WHERE sysdate between start_time and end_time;
Previous Topic: PLS-00457:"expressions have to be of SQL types"
Next Topic: How to findout the table and column name..(Urgent)
Goto Forum:
  


Current Time: Wed Dec 07 14:49:56 CST 2016

Total time taken to generate the page: 0.14829 seconds