How to take first 5 records according to date as well as time from two tables using sql?

Question

0.00/5 (No votes)

See more:

First Table : Exam Table
ex1 2014-08-07 13:21:48.000
ex2 2014-08-07 14:21:48.000
ex3 2014-08-06 13:21:48.000
ex4 2014-08-05 13:21:48.000

Second Table :Lecture Table

l1 2014-08-07 11:21:48.000
l2 2014-08-07 12:21:48.000
l3 2014-08-06 11:21:48.000
l4 2014-08-05 12:21:48.000
I want to retrieve top 5 rows from both the table by calculating time and date of both simultaneously.This record corresponds to one student. I want to find out recent 5 exams or lectures attended by student. Expected Result is as follows :

ex2 2014-08-07 14:21:48.000
ex1 2014-08-07 13:21:48.000
l2 2014-08-07 12:21:48.000
l1 2014-08-07 11:21:48.000
ex3 2014-08-06 13:21:48.000

While displaying result, time and date both should be compared properly. Can anyone tell me sql query for this?

Posted 11-Aug-14 0:09am

m-shraddha

Add a Solution

Comments

Herman<T>.Instance 12-Aug-14 4:20am

what is the link between both tables. Any ID?

4 solutions

Solution 4

Here is a way of getting the desired results without using a common table expression (CTE).

SQL

--Data setup
--create tables
declare @Exam as table (id varchar(5), theDateTime datetime);
declare @Lecture as table (id varchar(5), theDateTime datetime);

--populate tables
insert into @Exam values ('ex1','2014-08-07 13:21:48.000');
insert into @Exam values ('ex2','2014-08-07 14:21:48.000');
insert into @Exam values ('ex3','2014-08-06 13:21:48.000');
insert into @Exam values ('ex4','2014-08-05 13:21:48.000');

insert into @Lecture values ('l1','2014-08-07 11:21:48.000');
insert into @Lecture values ('l2','2014-08-07 12:21:48.000');
insert into @Lecture values ('l3','2014-08-06 11:21:48.000');
insert into @Lecture values ('l4','2014-08-05 12:21:48.000')

SQL

--query, not using CTE
select top 5
    id,
    theDateTime
from (
    select id, theDateTime from @Exam
    union all
    select id, theDateTime from @Lecture
) ExamAndLecture
order by theDateTime desc;

I am not sure which version will run faster, but I assume there will not be much difference in the speed of execution.
I have included the cte version below so you may test and compare the difference or similarity in the query structure.

SQL

--query, using CTE
with ExamAndLecture(id, theDateTime) as (
    select id, theDateTime from @Exam
    union all
    select id, theDateTime from @Lecture
)
select top 5
    id,
    theDateTime
from ExamAndLecture
order by theDateTime desc;

Posted 11-Aug-14 22:16pm

jaket-cp

Add a Solution

Add your solution here

Treat my content as plain text, not as HTML

Preview 0

…

Existing Members

Sign in to your account

...or Join us

Download, Vote, Comment, Publish.

Your Email
Password
Forgot your password?

Your Email
This email is in use. Do you need your password?
Optional Password

I have read and agree to the Terms of Service and Privacy Policy
Please subscribe me to the CodeProject newsletters

When answering a question please:

Read the question carefully.
Understand that English isn't everyone's first language so be lenient of bad spelling and grammar.
If a question is poorly phrased then either ask for clarification, ignore it, or edit the question and fix the problem. Insults are not welcome.
Don't tell someone to read the manual. Chances are they have and don't get it. Provide an answer or move on to the next question.

Let's work to help developers, not make them feel stupid.

This content, along with any associated source code and files, is licensed under The Code Project Open License (CPOL)

Mayank Vashishtha · Accepted Answer · 2014-08-11T01:33:00

Solution 2

SQL

;WITH MyCte(type, date) AS
(
SELECT LectureID, StartDateTime FROM lectureTable
UNION ALL
SELECT Exam_ID, DateOfExam FROM examTable
)

SELECT TOP 5 * FROM MyCte ORDER BY 2 DESC

Posted 11-Aug-14 1:33am

Mayank Vashishtha

v2

Comments

m-shraddha 11-Aug-14 7:46am

'MyCte' has fewer columns than were specified in the column list.

Mayank Vashishtha 11-Aug-14 8:02am

This code must work. I've just checked it. Working fine. Try selecting the complete query from ; to DESC. then try and execute it.

m-shraddha · Accepted Answer · 2014-08-11T18:10:00

Solution 3

SQL

;WITH MyCte(EType,EventDate) AS
(
SELECT 'Exam',E.DateOfExam FROM tbl_ExamCreation E
UNION ALL
SELECT 'Lecture',LS.StartDateTime FROM tbl_LectureScheduling LS
)
 
SELECT EType,EventDate FROM MyCte ORDER BY 2 DESC

This is Perfect Soluction. As we are selecting two columns, we have to mention two column name after MyCTe. as well as after last SELECT statement.

Posted 11-Aug-14 18:10pm

m-shraddha

Updated 11-Aug-14 22:23pm

v3

Comments

jaket-cp 12-Aug-14 3:59am

Solution 1

SQL

;WITH MyCte(type, date) AS
(
SELECT * FROM examTable
UNION ALL
SELECT * FROM lectureTable
)

SELECT TOP 5 * FROM MyCte ORDER BY 2 DESC

Posted 11-Aug-14 0:21am

Mayank Vashishtha

Comments

m-shraddha 11-Aug-14 6:31am

That semicolon before WITH is part of the query or it was just mistyped?

Mayank Vashishtha 11-Aug-14 6:32am

part of the query. that is the syntax of cte(common table expression).

If it solves your problem, kindly rate it.

m-shraddha 11-Aug-14 6:36am

Msg 205, Level 16, State 1, Line 1
All queries combined using a UNION, INTERSECT or EXCEPT operator must have an equal number of expressions in their target lists.

m-shraddha 11-Aug-14 6:37am

can you please tell me what is this error indicates?

Mayank Vashishtha 11-Aug-14 6:41am

It happens when your inner queries combined with UNION send different columns. Share your table structure with column names, I'll send you the updated query.

m-shraddha 12-Aug-14 0:11am

thanks for help

soumyajayaraj 11-Aug-14 6:43am

That means that the columns in yours tables are different. Try specifying the column names instead of select *. Both the select queries should return same number of columns.

m-shraddha 12-Aug-14 0:11am

thanks for suggestions

m-shraddha 11-Aug-14 6:51am

Lecture table from which I am comparing StartDateTime column

LectureScheduleID bigint
LectureID bigint
StartDateTime datetime
EndDateTime datetime
Status nvarchar(10)
CreatedBy int
CreatedDatetime datetime
UpdatedBy int
UpdatedDatetime datetime

-----------------------------
Exam table from which I am comparing DateOfExam column
Exam_ID int
ExamName nvarchar(250)
Course_ID int
Batch_ID int
Subject_ID int
DateOfExam datetime
ExamEndDatetime datetime
Grade_ID int
Status nvarchar(10)
CreatedBy int
CretedDatetime datetime
UpdatedBy int
UpdatedDatetime datetime

Now I want to compare StartDateTime and DateOfExam