Next Greater than Value

Question

0.00/5 (No votes)

See more:

i Want to make Report about A vehicle
there is 2 table
1) Vehicle
2) Vehicle-schedule

1) Vehicle

COlUMN 1 | COLUMN 2
Vehicle-Code | VEHICLE-KM
0000000001 | 1000
0000000002 | 2000
0000000003 | 5000
0000000004 | 8000

2) Vehicle-schedule

COlUMN 1 | COLUMN 2
id | VEHICLE-SCHEDULE-FOR-REVISIT
01 | 2000
02 | 5000
03 | 8000
04 | 12000

These two tables
but i need join where Vehile,Colum1 and VehicleScdule Colum2 Max value

Example

3) Vehicle-join

COlUMN 1 | COLUMN 2
Vehicle-Code | VEHICLE-KM | VEHICLE-SCHEDULE-FOR-REVISIT
0000000001 | 1000 | 2000
0000000002 | 2000 | 5000
0000000003 | 5000 | 8000
0000000004 | 8000 | 12000

how I select Next Greater than value from Vehicle Schedule table

Posted 5-Jan-14 18:25pm

saimm

Add a Solution

Comments

Amir Mahfoozi 6-Jan-14 0:51am

What is the id field in Vehicle-schedule table ? is it for its own table or it points to a vehicle ? In both states it is not a good design.

saimm 6-Jan-14 1:23am

yeah its not good design but i cant make change bcoz huge data depend on it

2 solutions

Solution 1

Hi,

Those two tables should have relation,

For example, you can add one column on table 1 or table 2, ill use table 2

1. Ill add Vechicle-Code column on table2.
2. Then i'll assign its same code on the table 1
3. heres the query

"Select table1.Vehicle-Code, table1.Vehicle-km,table2.VEHICLE-SCHEDULE-FOR-REVISIT from table1, table2 where table1.VehicleCode = table2.VehicleCode"

Hope this helps,
Elieser Legaspi

Posted 5-Jan-14 18:37pm

elieser0213

Comments

saimm 6-Jan-14 0:42am

elieser,
no dear
i dont want Equal means table1.km= table2.km"
i want Next greater than means table1.VehicleCode = max(table2.km)"
but there is max(table2.km) should be less than Table1.km

saimm 6-Jan-14 0:46am

NOw Clear Elieser?

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)

Peter Leow · Accepted Answer · 2014-01-05T19:06:00

Solution 2

SQL

SELECT v.[Vehicle-Code], v.[VEHICLE-KM],
(
SELECT MIN(s.[VEHICLE-SCHEDULE-FOR-REVISIT]) FROM [Vehicle-Schedule] s
WHERE s.[VEHICLE-SCHEDULE-FOR-REVISIT] > v.[VEHICLE-KM]
) AS [VEHICLE-SCHEDULE-FOR-REVISIT]
FROM Vehicle v

Posted 5-Jan-14 19:06pm

Peter Leow

Comments

saimm 6-Jan-14 1:28am

yes its working thanks Peter
i was bz to make this query from last day (:

Peter Leow 6-Jan-14 1:34am

You are welcome.