conditional select statment
-
i have these data in the database table, Meetings MeetingID Details Hour Minute 1 Meeting Details1 14 10 2 Meeting Details2 14 15 how can i select all the fields of MeetingID 2?
SELECT MeetingID, Hour, Minute, Details FROM Meetings WHERE (Hour = (SELECT MAX(Hour) FROM Meetings)) AND (Minute = (SELECT MAX(Minute) FROM Meetings))but this code doesn't fetch any record -
i have these data in the database table, Meetings MeetingID Details Hour Minute 1 Meeting Details1 14 10 2 Meeting Details2 14 15 how can i select all the fields of MeetingID 2?
SELECT MeetingID, Hour, Minute, Details FROM Meetings WHERE (Hour = (SELECT MAX(Hour) FROM Meetings)) AND (Minute = (SELECT MAX(Minute) FROM Meetings))but this code doesn't fetch any recordOf course it doesn't - the row with the max hour might not be the same row as the row with the max minute. If you stored the meeting time in a single field (of type datetime or smalldatetime) which would be the right solution, this would be easy.
SELECT TOP 1 * FROM Meeting ORDER BY MeetingDateTime DESC
As it is with your table you can just do
SELECT TOP 1 * FROM MEeting Order BY Hour DESC, Minute DESC
-
i have these data in the database table, Meetings MeetingID Details Hour Minute 1 Meeting Details1 14 10 2 Meeting Details2 14 15 how can i select all the fields of MeetingID 2?
SELECT MeetingID, Hour, Minute, Details FROM Meetings WHERE (Hour = (SELECT MAX(Hour) FROM Meetings)) AND (Minute = (SELECT MAX(Minute) FROM Meetings))but this code doesn't fetch any recordwhether you search for MeetingID =2 or for which where Meeting time is maximum I think you want to get the all detail of meeting for which meeting time is highest. use following query
SELECT * FROM mettings WHERE (hour*60+minute) =(select max(hour*60+minute) from mettings)hope this helpsRupesh Kumar Swami Software Developer, Integrated Solution, Bikaner (India) My Company Award: Best VB.NET article of June 2008: Create Column Charts Using OWC11
-
Of course it doesn't - the row with the max hour might not be the same row as the row with the max minute. If you stored the meeting time in a single field (of type datetime or smalldatetime) which would be the right solution, this would be easy.
SELECT TOP 1 * FROM Meeting ORDER BY MeetingDateTime DESC
As it is with your table you can just do
SELECT TOP 1 * FROM MEeting Order BY Hour DESC, Minute DESC
-
whether you search for MeetingID =2 or for which where Meeting time is maximum I think you want to get the all detail of meeting for which meeting time is highest. use following query
SELECT * FROM mettings WHERE (hour*60+minute) =(select max(hour*60+minute) from mettings)hope this helpsRupesh Kumar Swami Software Developer, Integrated Solution, Bikaner (India) My Company Award: Best VB.NET article of June 2008: Create Column Charts Using OWC11
-
whether you search for MeetingID =2 or for which where Meeting time is maximum I think you want to get the all detail of meeting for which meeting time is highest. use following query
SELECT * FROM mettings WHERE (hour*60+minute) =(select max(hour*60+minute) from mettings)hope this helpsRupesh Kumar Swami Software Developer, Integrated Solution, Bikaner (India) My Company Award: Best VB.NET article of June 2008: Create Column Charts Using OWC11
-
Rupesh Kumar Swami wrote:
SELECT * FROM mettings WHERE (hour*60+minute) =(select max(hour*60+minute) from mettings)
:wtf: Why? Multiply the hour by 60 and add the minutes? If anything the other way round, surely?
the math is fine, it is calculating minutes since midnight. On a big table it could be slow... :)
Luc Pattyn [Forum Guidelines] [My Articles]
- before you ask a question here, search CodeProject, then Google - the quality and detail of your question reflects on the effectiveness of the help you are likely to get - use the code block button (PRE tags) to preserve formatting when showing multi-line code snippets
-
Rupesh Kumar Swami wrote:
SELECT * FROM mettings WHERE (hour*60+minute) =(select max(hour*60+minute) from mettings)
:wtf: Why? Multiply the hour by 60 and add the minutes? If anything the other way round, surely?
J4amieC wrote:
Why? Multiply the hour by 60 and add the minutes?
if there are multiple records, for which Metting time is equal to Highest Metting time, then it display all records. However your suggestion is better.
Rupesh Kumar Swami Software Developer, Integrated Solution, Bikaner (India) My Company Award: Best VB.NET article of June 2008: Create Column Charts Using OWC11