Top clause , Union and Order by

Please start any new threads on our new site at https://forums.sqlteam.com. We've got lots of great SQL Server experts to answer whatever question you can come up with.

All Forums

General SQL Server Forums

New to SQL Server Programming

Top clause , Union and Order by

Author

Topic

flamblaster
Constraint Violating Yak Guru

384 Posts

Posted - 2007-12-07 : 13:30:21

Hi,

I'm currently have a problem with a query using a top clause. When I run it by itself as a single query, I have no problems and the results are valid. However, if I try duplicate the query after a union clause, the order by ... desc doesn't order properly.

The following is the query I'm using along with the results. Then I'll have the query I was trying to unite and the results (date ranges selected were the same in both):

QUERY 1

select top 1 (s.ldate), v.mdtid, po.odometer, pi.odometer, (pi.odometer-po.odometer) as 'Total Miles'

from EventStrings ES

JOIN schedules s
ON ES.SchId=S.SchId
JOIN vehicles v
ON v.vehicleid=es.vehicleid
JOIN Events PO
ON PO.schid=es.schid
AND PO.EvStrId=ES.EvStrId
AND po.activity=4
JOIN Events PI
ON PI.schid=es.schid
AND PI.EvStrId=ES.EvStrId
AND pi.activity=3

WHERE es.providerid in (0,1,4)
and s.ldate>=?
and s.ldate<=?
and v.mdtid=20411

order by s.ldate desc

RESULTS 1

DATE MDT ID PU Odometer DO Odometer Total Miles
12/6/2007 20411 12810.6 12874.5 63.9

QUERY 2 (with Union)

select top 1 (s.ldate), v.mdtid, po.odometer, pi.odometer, (pi.odometer-po.odometer) as 'Total Miles'

from EventStrings ES

JOIN schedules s
ON ES.SchId=S.SchId
JOIN vehicles v
ON v.vehicleid=es.vehicleid
JOIN Events PO
ON PO.schid=es.schid
AND PO.EvStrId=ES.EvStrId
AND po.activity=4
JOIN Events PI
ON PI.schid=es.schid
AND PI.EvStrId=ES.EvStrId
AND pi.activity=3

WHERE es.providerid in (0,1,4)
and s.ldate>=[From Date,Date]
and s.ldate<=[To Date,Date]
and v.mdtid=20411

Union

select top 1 (s.ldate), v.mdtid, po.odometer, pi.odometer, (pi.odometer-po.odometer) as 'Total Miles'

from EventStrings ES

JOIN schedules s
ON ES.SchId=S.SchId
JOIN vehicles v
ON v.vehicleid=es.vehicleid
JOIN Events PO
ON PO.schid=es.schid
AND PO.EvStrId=ES.EvStrId
AND po.activity=4
JOIN Events PI
ON PI.schid=es.schid
AND PI.EvStrId=ES.EvStrId
AND pi.activity=3

WHERE es.providerid in (0,1,4)
and s.ldate>=?
and s.ldate<=?
and v.mdtid=2642

order by s.ldate desc

RESULTS 2

DATE MDT ID PU Odometer DO Odometer Total Miles
4/10/2007 20411 1207.2 1252.5 45.3
1/2/2007 2642 193652.6 193817 164.4

As you can see, the results are sorted very differently. Is there any way to have the order by apply to both queries?

Thanks!
Craig

jhocutt
Constraint Violating Yak Guru

385 Posts

Posted - 2007-12-07 : 13:57:58

select * from (
select top 1 s.ldate, v.mdtid, po.odometer, pi.odometer, (pi.odometer-po.odometer) as 'Total Miles'
from EventStrings ES
JOIN schedules s
ON ES.SchId=S.SchId
JOIN vehicles v
ON v.vehicleid=es.vehicleid
JOIN Events PO
ON PO.schid=es.schid
AND PO.EvStrId=ES.EvStrId
AND po.activity=4
JOIN Events PI
ON PI.schid=es.schid
AND PI.EvStrId=ES.EvStrId
AND pi.activity=3
WHERE es.providerid in (0,1,4)
and s.ldate>=[From Date,Date]
and s.ldate<=[To Date,Date]
and v.mdtid=20411
order by s.ldate desc
Union
select top 1 s.ldate, v.mdtid, po.odometer, pi.odometer, (pi.odometer-po.odometer) as 'Total Miles'
from EventStrings ES
JOIN schedules s
ON ES.SchId=S.SchId
JOIN vehicles v
ON v.vehicleid=es.vehicleid
JOIN Events PO
ON PO.schid=es.schid
AND PO.EvStrId=ES.EvStrId
AND po.activity=4
JOIN Events PI
ON PI.schid=es.schid
AND PI.EvStrId=ES.EvStrId
AND pi.activity=3
WHERE es.providerid in (0,1,4)
and s.ldate>=?
and s.ldate<=?
and v.mdtid=2642
order by s.ldate desc
) a
order by ldate desc

"God does not play dice" -- Albert Einstein
"Not only does God play dice, but he sometimes throws them where they cannot be seen."
-- Stephen Hawking

flamblaster
Constraint Violating Yak Guru

384 Posts

Posted - 2007-12-07 : 14:12:37

jhocutt, Thanks for the reply. I tried your query on both interfaces that I have to work with and got the following errors:

1st
SQLExecDirect returned SQL_ERROR (from HSTMT), SqlState=37000, Native Error=16945, CODBC_ERROR=0
Message=[Microsoft][ODBC SQL Server Driver][SQL Server]The cursor was not declared.

2nd
[Microsoft][ODBC SQL Server Driver]The column 'odometer' was specified multiple times for 'a'

Any ideas?

jhocutt
Constraint Violating Yak Guru

385 Posts

Posted - 2007-12-07 : 14:18:51

That is what I get for not looking closer

select * from (
select top 1 s.ldate as LDATE, v.mdtid as MTID, po.odometer as PO_ODOMETER, pi.odometer as PI_ODOMETER, (pi.odometer-po.odometer) as 'Total Miles'
from EventStrings ES
JOIN schedules s
ON ES.SchId=S.SchId
JOIN vehicles v
ON v.vehicleid=es.vehicleid
JOIN Events PO
ON PO.schid=es.schid
AND PO.EvStrId=ES.EvStrId
AND po.activity=4
JOIN Events PI
ON PI.schid=es.schid
AND PI.EvStrId=ES.EvStrId
AND pi.activity=3
WHERE es.providerid in (0,1,4)
and s.ldate>=[From Date,Date]
and s.ldate<=[To Date,Date]
and v.mdtid=20411
order by s.ldate desc
Union
select top 1 s.ldate as LDATE, v.mdtid as MTID, po.odometer as PO_ODOMETER, pi.odometer as PI_ODOMETER, (pi.odometer-po.odometer) as 'Total Miles'
from EventStrings ES
JOIN schedules s
ON ES.SchId=S.SchId
JOIN vehicles v
ON v.vehicleid=es.vehicleid
JOIN Events PO
ON PO.schid=es.schid
AND PO.EvStrId=ES.EvStrId
AND po.activity=4
JOIN Events PI
ON PI.schid=es.schid
AND PI.EvStrId=ES.EvStrId
AND pi.activity=3
WHERE es.providerid in (0,1,4)
and s.ldate>=?
and s.ldate<=?
and v.mdtid=2642
order by s.ldate desc
) a
order by LDATE desc

"God does not play dice" -- Albert Einstein
"Not only does God play dice, but he sometimes throws them where they cannot be seen."
-- Stephen Hawking

flamblaster
Constraint Violating Yak Guru

384 Posts

Posted - 2007-12-07 : 14:28:09

jhocutt,

That fixed the error, but then I get the incorrect order by again.

Although the correct results should be:

DATE MDT ID PU Odometer DO Odometer Total Miles
12/6/2007 20411 12810.6 12874.5 63.9
02/28/2007 2642 197868.50 197833.50 35.00

It returns the following results:

DATE MDT ID PU Odometer DO Odometer Total Miles
4/10/2007 20411 1207.2 1252.5 45.3
2/12/2007 2642 196595.4 196670.9 75.5

Any more thoughts? I'm not sure why the order by isn't working in the union??

flamblaster
Constraint Violating Yak Guru

384 Posts

Posted - 2007-12-07 : 15:02:04

Thanks for your help...looking at your response, I tried something. I added order by s.ldate desc after each query, but before each union and somehow it worked. I thought that the order by had to be after the last union, but I'm not gonna complain! Thanks again for your help.

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources