How I Order Date From Max To Min With Multiple Table?

Question

0.00/5 (No votes)

See more:

I used Union to select multiple table in one crystal report using one table in dataSet, how i display data from max date to min date

Posted 1-Feb-16 21:12pm

Member 12244977

Add a Solution

Comments

Jawad Ahmed Tanoli 2-Feb-16 3:44am

you can do in query ?where is problem

3 solutions

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)

Kornfeld Eliyahu Peter · Answer 1 · 2016-02-01T21:44:00

Solution 1

When you combine result sets using UNION the ORDER BY part must be on the last query and it will apply to all the combined record sets...
If you have to apply different ORDER BY to each and every query (like when you have TOP(n) in them so ORDER BY makes all the differences), you have to enclose the queries like this:

SQL

SELECT * FROM (SELECT fld1, fld1 FROM tbl1 ORDER BY fld1))
UNION  
SELECT * FROM (SELECT fld1, fld1 FROM tbl2 ORDER BY fld1))

Posted 1-Feb-16 21:44pm

Kornfeld Eliyahu Peter

Comments

Member 12244977 2-Feb-16 3:58am

this is code how i add order by here :-

select Cars.Car_id,convert(varchar, Change_date,101) as Change_date , Service_Type.Servce_desc, Current_dist,Prev_distance as Prev_dist,(Current_dist - Prev_distance) as Change_dist,value_Tashhem as Value,NULL as [Qty] from Tashhem s1 inner join cars on s1.Car_id= Cars.Car_id inner join Service_Type on s1.Servce_id = Service_Type.Servce_id where s1.Car_id = 555 and s1.Change_date between 1-1-2000 and 1-1-2016
union
select Cars.Car_id,convert(varchar, Change_date,101) as Change_date, Service_Type.Servce_desc,Current_Dist as Current_dist,Prev_Dist as Prev_dist,(Current_dist - Prev_Dist) as Change_dist,Value_Oil as Value,QTY as Qty from Change_Oil s1 inner join cars on s1.Car_id= Cars.Car_id inner join Service_Type on s1.Servce_id = Service_Type.Servce_id where s1.Car_id =555 and s1.Change_date between 1-1-2000 and 1-1-2016
union
select Cars.Car_id,convert(varchar, Change_Date,101) as Change_date, Service_Type.Servce_desc,Current_dist ,Prev_dist ,(Current_dist - Prev_dist) as Change_dist,Value_Fuel as Value,Qty from Full_Fuel s1 inner join cars on s1.Car_id= Cars.Car_id inner join Service_Type on s1.Servce_id = Service_Type.Servce_id where s1.Car_id =555 and s1.Change_date between 1-1-2000 and 1-1-2016
union
select Cars.Car_id,convert(varchar, Change_date,101) as Change_date, Service_Type.Servce_desc,Current_dist ,Prev_dist ,(convert(float,Current_dist) - convert(float,Prev_dist)) as Change_dist,value_tire as Value,NULL as [Qty] from Change_Tire s1 inner join cars on s1.Car_id= Cars.Car_id inner join Service_Type on s1.Servce_id = Service_Type.Servce_id where s1.Car_id =555 and s1.Change_date between 1-1-2000 and 1-1-2016
union
select Cars.Car_id,convert(varchar, Change_Date,101) as Change_date, Service_Type.Servce_desc,Current_dist ,Prev_dist ,(convert(float,Current_dist) - convert(float,Prev_dist)) as Change_dist,Value_Batteries as Value,NULL as [Qty] from Change_Batteries s1 inner join cars on s1.Car_id= Cars.Car_id inner join Service_Type on s1.Servce_id = Service_Type.Servce_id where s1.Car_id =555 and s1.Change_date between 1-1-2000 and 1-1-2016
union
select Auto_Parts.Car_id,convert(varchar, Auto_Parts.Change_date,101) as Change_date,Service_Type.Servce_desc,Auto_Parts.Current_dist,Auto_Parts.Prev_dist,(convert(float,Current_dist) - convert(float,Prev_dist)) as Change_dist,(s1.Value_Part) as Value,NULL as [Qty] from Auto_Part_Details s1 inner join Auto_Parts on s1.Job_Card = Auto_Parts.Job_Card inner join Service_Type on Auto_Parts.Servce_id = Service_Type.Servce_id where Auto_Parts.Car_id = 555 and Auto_Parts.Change_date between 1-1-2000 and 1-1-2016

Jawad Ahmed Tanoli · Answer 2 · 2016-02-01T21:48:00

Solution 2

SQL

SELECT  * 
FROM 
        (
            SELECT *, sortby FROM tblA
            UNION ALL 
            SELECT *, sortby FROM tblB
        ) tbl
ORDER   BY sortby DESC

Posted 1-Feb-16 21:48pm

Jawad Ahmed Tanoli

Comments

Member 12244977 2-Feb-16 4:18am

why order by not work here?

select Auto_Parts.Car_id,convert(varchar, Auto_Parts.Change_date,101) as Change_date,Service_Type.Servce_desc,Auto_Parts.Current_dist,Auto_Parts.Prev_dist,(convert(float,Current_dist) - convert(float,Prev_dist)) as Change_dist,(s1.Value_Part) as Value,NULL as [Qty] from Auto_Part_Details s1 inner join Auto_Parts on s1.Job_Card = Auto_Parts.Job_Card inner join Service_Type on Auto_Parts.Servce_id = Service_Type.Servce_id where Auto_Parts.Car_id = 555 and Auto_Parts.Change_date between 1-1-2000 and 1-1-201 order by s1.Change_date

Jawad Ahmed Tanoli 2-Feb-16 9:10am

there is no union in your query
try
order by convert(varchar, Auto_Parts.Change_date,101)

Jawad Ahmed Tanoli 2-Feb-16 9:15am

select * from (select Cars.Car_id,convert(varchar, Change_date,101) as Change_date , Service_Type.Servce_desc, Current_dist,Prev_distance as Prev_dist,(Current_dist - Prev_distance) as Change_dist,value_Tashhem as Value,NULL as [Qty] from Tashhem s1 inner join cars on s1.Car_id= Cars.Car_id inner join Service_Type on s1.Servce_id = Service_Type.Servce_id where s1.Car_id = 555 and s1.Change_date between 1-1-2000 and 1-1-2016
union
select Cars.Car_id,convert(varchar, Change_date,101) as Change_date, Service_Type.Servce_desc,Current_Dist as Current_dist,Prev_Dist as Prev_dist,(Current_dist - Prev_Dist) as Change_dist,Value_Oil as Value,QTY as Qty from Change_Oil s1 inner join cars on s1.Car_id= Cars.Car_id inner join Service_Type on s1.Servce_id = Service_Type.Servce_id where s1.Car_id =555 and s1.Change_date between 1-1-2000 and 1-1-2016
union
select Cars.Car_id,convert(varchar, Change_Date,101) as Change_date, Service_Type.Servce_desc,Current_dist ,Prev_dist ,(Current_dist - Prev_dist) as Change_dist,Value_Fuel as Value,Qty from Full_Fuel s1 inner join cars on s1.Car_id= Cars.Car_id inner join Service_Type on s1.Servce_id = Service_Type.Servce_id where s1.Car_id =555 and s1.Change_date between 1-1-2000 and 1-1-2016
union
select Cars.Car_id,convert(varchar, Change_date,101) as Change_date, Service_Type.Servce_desc,Current_dist ,Prev_dist ,(convert(float,Current_dist) - convert(float,Prev_dist)) as Change_dist,value_tire as Value,NULL as [Qty] from Change_Tire s1 inner join cars on s1.Car_id= Cars.Car_id inner join Service_Type on s1.Servce_id = Service_Type.Servce_id where s1.Car_id =555 and s1.Change_date between 1-1-2000 and 1-1-2016
union
select Cars.Car_id,convert(varchar, Change_Date,101) as Change_date, Service_Type.Servce_desc,Current_dist ,Prev_dist ,(convert(float,Current_dist) - convert(float,Prev_dist)) as Change_dist,Value_Batteries as Value,NULL as [Qty] from Change_Batteries s1 inner join cars on s1.Car_id= Cars.Car_id inner join Service_Type on s1.Servce_id = Service_Type.Servce_id where s1.Car_id =555 and s1.Change_date between 1-1-2000 and 1-1-2016
union
select Auto_Parts.Car_id,convert(varchar, Auto_Parts.Change_date,101) as Change_date,Service_Type.Servce_desc,Auto_Parts.Current_dist,Auto_Parts.Prev_dist,(convert(float,Current_dist) - convert(float,Prev_dist)) as Change_dist,(s1.Value_Part) as Value,NULL as [Qty] from Auto_Part_Details s1 inner join Auto_Parts on s1.Job_Card = Auto_Parts.Job_Card inner join Service_Type on Auto_Parts.Servce_id = Service_Type.Servce_id where Auto_Parts.Car_id = 555 and Auto_Parts.Change_date between 1-1-2000 and 1-1-2016) as t order by t.Change_date

Pravin.Bhosale01 · Answer 3 · 2016-02-01T21:52:00

Solution 3

Try this...

C#

CrystalReport1.SelectionFormula = "{Exp.ETD} In Date(" & Format(fromDateVariableHere, "yyyy,mm,dd") & ") To Date(" & Format(toDateVariableHere, "yyyy,mm,dd"))"

Posted 1-Feb-16 21:52pm

Pravin.Bhosale01