asked on

Sql Query using except order by

Hi ,

I am using Sql Server 2005

What I want is I am having two queries
I want to retrieve the links other than first 15.

Total links - first 15 links. I want to use order by in both the queries.
Order by not working with except.

thanks

select * from Links order by DateStamp asc
except
select top 15 * from Links order by DateStamp asc

Open in new window

appari

try this

Select * from (select top 100 percent * from Links order by DateStamp asc) A
except
Select * from (select top 15 * from Links order by DateStamp asc) A
order by DateStamp asc

Lee Wadwell

Hi Saroj13,

The order by would be applied to each SQL prior to the except - after the except the order can be different. To get the order you want you should do an order by on the result , i.e. SELECT * FROM (your query) as v ORDER BY DateStamp asc. In this case the ORDER BY in your top SQL may be unnecessary.

lwadwell

Saroj13

ASKER

Hi lwadwell:,

I need order by in both the queries.

What I want is . I want all the data according to date.
Then i want top 15 from this all data. Then I want to subtract it.

Thanks

Lee Wadwell

Saroj13,

I understand that you need the ORDER BY for the TOP 15 ... that is fine. The EXCEPT returns those rows from the top query not returned from the bottom query - it does not need the top query to be sorted (it will do that itself). I was suggesting:

SELECT * FROM (
select * from Links
except
select top 15 * from Links order by DateStamp asc
) as v ORDER BY DateStamp asc

lwadwell

ASKER CERTIFIED SOLUTION

Sharath S

membership

This solution is only available to members.

To access this solution, you must be a member of Experts Exchange.

Start Free Trial

TejasShahMscIT

Hi,

Try this:

SELECT * 
FROM (
select * from Links 
except
select top 15 * from Links order by DateStamp asc
) x 
ORDER BY order by DateStamp asc
 
Thanks,
Tejas

Open in new window

Guy Hengel [angelIII / a3]

this would do also:

select * from Links 
where primary_key_field not in (  select top 15 primary_key_field from Links order by DateStamp asc )
order by DateStamp asc

Open in new window

servicegroup

I just stumbed upon something while trying to fix a similar problem. You can order by SQL's name for this field. In my case I had, SELECT field1 AS field1, field2 AS field2...Except...

I ordered by 00004 (the 4th field) at the end of the statement.

I hope this helps someone