April 4, 2012 at 8:55 pm
My query is not working - issue happens when I add the last WHERE clause. Otherwise it works fine. I need to get sort the rows before selecting the first 15. Any ideas as to why it is not working? Help is greatly appreciated.
SELECT ANIM_ID, ANIM_HEIGHT_ON_ARRIVAL, (ANIM_HEIGHT_ON_ARRIVAL - (SELECT AVG(ANIM_HEIGHT_ON_ARRIVAL) FROM ANIMAL)) AS HEIGHT_DIFFERENCE
FROM ANIMAL
WHERE ANIM_HEIGHT_ON_ARRIVAL >
(SELECT AVG(ANIM_HEIGHT_ON_ARRIVAL) FROM ANIMAL)
ORDER BY HEIGHT_DIFFERENCE ASC
WHERE ROWNUM < 16;
April 4, 2012 at 9:07 pm
rupes0610 (4/4/2012)
My query is not working - issue happens when I add the last WHERE clause. Otherwise it works fine. I need to get sort the rows before selecting the first 15. Any ideas as to why it is not working? Help is greatly appreciated.SELECT ANIM_ID, ANIM_HEIGHT_ON_ARRIVAL, (ANIM_HEIGHT_ON_ARRIVAL - (SELECT AVG(ANIM_HEIGHT_ON_ARRIVAL) FROM ANIMAL)) AS HEIGHT_DIFFERENCE
FROM ANIMAL
WHERE ANIM_HEIGHT_ON_ARRIVAL >
(SELECT AVG(ANIM_HEIGHT_ON_ARRIVAL) FROM ANIMAL)
ORDER BY HEIGHT_DIFFERENCE ASC
WHERE ROWNUM < 16;
Because you already have a where clause for the outer select:
WHERE ANIM_HEIGHT_ON_ARRIVAL >
(SELECT AVG(ANIM_HEIGHT_ON_ARRIVAL) FROM ANIMAL)
If ROWNUM is a column in your database, you need to add that to you other where clause:
WHERE ANIM_HEIGHT_ON_ARRIVAL >
(SELECT AVG(ANIM_HEIGHT_ON_ARRIVAL) FROM ANIMAL) AND ROWNUM <= 16
April 4, 2012 at 9:08 pm
rupes0610 (4/4/2012)
My query is not working - issue happens when I add the last WHERE clause. Otherwise it works fine. I need to get sort the rows before selecting the first 15. Any ideas as to why it is not working? Help is greatly appreciated.SELECT ANIM_ID, ANIM_HEIGHT_ON_ARRIVAL, (ANIM_HEIGHT_ON_ARRIVAL - (SELECT AVG(ANIM_HEIGHT_ON_ARRIVAL) FROM ANIMAL)) AS HEIGHT_DIFFERENCE
FROM ANIMAL
WHERE ANIM_HEIGHT_ON_ARRIVAL >
(SELECT AVG(ANIM_HEIGHT_ON_ARRIVAL) FROM ANIMAL)
ORDER BY HEIGHT_DIFFERENCE ASC
WHERE ROWNUM < 16;
Well, you have used the where clause twice
You have:
SELECT ANIM_ID, ANIM_HEIGHT_ON_ARRIVAL, (ANIM_HEIGHT_ON_ARRIVAL - (SELECT AVG(ANIM_HEIGHT_ON_ARRIVAL) FROM ANIMAL)) AS HEIGHT_DIFFERENCE
FROM ANIMAL
WHERE ANIM_HEIGHT_ON_ARRIVAL >
(SELECT AVG(ANIM_HEIGHT_ON_ARRIVAL) FROM ANIMAL)
ORDER BY HEIGHT_DIFFERENCE ASC
WHERE ROWNUM < 16;
Try formatting the code so it is easier to follow, and try this change:
SELECT ANIM_ID, ANIM_HEIGHT_ON_ARRIVAL, (ANIM_HEIGHT_ON_ARRIVAL - (SELECT AVG(ANIM_HEIGHT_ON_ARRIVAL) FROM ANIMAL)) AS HEIGHT_DIFFERENCE
FROM ANIMAL
WHERE ANIM_HEIGHT_ON_ARRIVAL >
(SELECT AVG(ANIM_HEIGHT_ON_ARRIVAL) FROM ANIMAL)
And Rownum < 16
ORDER BY HEIGHT_DIFFERENCE ASC
;
Jason...AKA CirqueDeSQLeil
_______________________________________________
I have given a name to my pain...MCM SQL Server, MVP
SQL RNNR
Posting Performance Based Questions - Gail Shaw[/url]
Learn Extended Events
April 4, 2012 at 9:12 pm
I need to sort all the rows and then select the first 15 with the smallest height difference. Your method selects the first 15 and then sorts them. (I need the opposite of your method). Thank you for your help though! 🙂
April 4, 2012 at 9:15 pm
rupes0610 (4/4/2012)
I need to sort all the rows and then select the first 15 with the smallest height difference. Your method selects the first 15 and then sorts them. (I need the opposite of your method). Thank you for your help though! 🙂
We couldn't have known that with only a post on a malformed query saying that it wasn't working.
Are there any other requirements?
Do you have sample data in order for somebody to provide you an answer?
Jason...AKA CirqueDeSQLeil
_______________________________________________
I have given a name to my pain...MCM SQL Server, MVP
SQL RNNR
Posting Performance Based Questions - Gail Shaw[/url]
Learn Extended Events
April 4, 2012 at 9:52 pm
rupes0610 (4/4/2012)
I need to sort all the rows and then select the first 15 with the smallest height difference. Your method selects the first 15 and then sorts them. (I need the opposite of your method). Thank you for your help though! 🙂
Okay, how about this?
SELECT TOP 15 ANIM_ID, ANIM_HEIGHT_ON_ARRIVAL, (ANIM_HEIGHT_ON_ARRIVAL - (SELECT AVG(ANIM_HEIGHT_ON_ARRIVAL) FROM ANIMAL)) AS HEIGHT_DIFFERENCE
FROM ANIMAL
WHERE ANIM_HEIGHT_ON_ARRIVAL >
(SELECT AVG(ANIM_HEIGHT_ON_ARRIVAL) FROM ANIMAL)
ORDER BY HEIGHT_DIFFERENCE ASC
;
Viewing 6 posts - 1 through 6 (of 6 total)
You must be logged in to reply to this topic. Login to reply