sql syntax help -sybase
I have a query .This is selecting records from 6 table and displaying.
I want only the FIRST record returning a15.title title to appear in the list, however when there are different entries in a14.processed processed, it returns a duplicate entry.
I have attempted to use group by a15.title but it returns no records whenever I put in a group by parameter.
If the record is coming from a single table then i know how to use row_number() over (partition).
But from many tables i not able to do it.
Any help appreciated.
I want only the FIRST record returning a15.title title to appear in the list, however when there are different entries in a14.processed processed, it returns a duplicate entry.
I have attempted to use group by a15.title but it returns no records whenever I put in a group by parameter.
If the record is coming from a single table then i know how to use row_number() over (partition).
But from many tables i not able to do it.
Any help appreciated.
select distinct a15.title title, a11.author author, a14.processed processed, a12.creation_date creation_day, a13.collection collection, a16.descr descr
from rv_bib_author a11, rv_bib_control a12, rv_item a13, rv_isbn a14, rv_title_inverted a15, collection a16 where a11.bib_id = a12.bib_id and a11.bib_id = a13.bib_id and a11.bib_id = a14.bib_id and a11.bib_id = a15.bib_id and a13.collection = a16.collection and (a13.collection in ('f', 'nf')and (a14.processed IS NOT NULL) and a12.creation_date between '03/01/2009' and '05/30/2019') order by a12.creation_date desc
Mortaza Doulaty
Try to re-write your query as a single view, then try to use ROW_NUMBER() on a view.
Hope this helps
select *
from (
select a15.title title, a11.author author, a14.processed processed, a12.creation_date creation_day, a13.collection collection, a16.descr descr, row_number() over ( partition by a15.title, a11.author, a14.processed, a12.creation_date, a13.collection, a16.descr order by a12.creation_date ) rnum
from rv_bib_author a11, rv_bib_control a12, rv_item a13, rv_isbn a14, rv_title_inverted a15, collection a16 where a11.bib_id = a12.bib_id and a11.bib_id = a13.bib_id and a11.bib_id = a14.bib_id and a11.bib_id = a15.bib_id and a13.collection = a16.collection and (a13.collection in ('f', 'nf')and (a14.processed IS NOT NULL) and a12.creation_date between '03/01/2009' and '05/30/2019') order by a12.creation_date desc ) as temp
where rnum = 1
ASKER
@rrjegan17:i am getting this error..
Msg 1033, Level 15, State 1, Line 4
The ORDER BY clause is invalid in views, inline functions, derived tables, subqueries, and common table expressions, unless TOP or FOR XML is also specified.
@Mortaza_doulaty:I am using this in a php script so i cant create a view...
Msg 1033, Level 15, State 1, Line 4
The ORDER BY clause is invalid in views, inline functions, derived tables, subqueries, and common table expressions, unless TOP or FOR XML is also specified.
@Mortaza_doulaty:I am using this in a php script so i cant create a view...
Try like this:
select * from (
select distinct
a15.title title,
a11.author author,
a14.processed processed,
a12.creation_date creation_day,
a13.collection collection,
a16.descr descr,
row_number() over ( partition by a15.title, a11.author, a14.processed, a12.creation_date, a13.collection, a16.descr order by a12.creation_date ) rnum
from rv_bib_author a11,
rv_bib_control a12,
rv_item a13,
rv_isbn a14,
rv_title_inverted a15,
collection a16
where a11.bib_id = a12.bib_id and
a11.bib_id = a13.bib_id and
a11.bib_id = a14.bib_id and
a11.bib_id = a15.bib_id and
a13.collection = a16.collection
and ( a13.collection in ('f', 'nf') and
(a14.processed IS NOT NULL) and
a12.creation_date between '03/01/2009' and '05/30/2019')
)a
where rnum = 1
order by creation_day
Try this one out:
select *
from (
select distinct a15.title title, a11.author author, a14.processed processed, a12.creation_date creation_day, a13.collection collection, a16.descr descr, row_number() over (order by a12.creation_date ) rnum
from rv_bib_author a11, rv_bib_control a12, rv_item a13, rv_isbn a14, rv_title_inverted a15, collection a16 where a11.bib_id = a12.bib_id and a11.bib_id = a13.bib_id and a11.bib_id = a14.bib_id and a11.bib_id = a15.bib_id and a13.collection = a16.collection and (a13.collection in ('f', 'nf')and (a14.processed IS NOT NULL) and a12.creation_date between '03/01/2009' and '05/30/2019') order by a12.creation_date desc ) as temp
where rnum = 1
ASKER
@rrjegan17 : same error....
Msg 1033, Level 15, State 1, Line 4
The ORDER BY clause is invalid in views, inline functions, derived tables, subqueries, and common table expressions, unless TOP or FOR XML is also specified.
Msg 1033, Level 15, State 1, Line 4
The ORDER BY clause is invalid in views, inline functions, derived tables, subqueries, and common table expressions, unless TOP or FOR XML is also specified.
Your title reads:
sql syntax help -sybase
however you added MS SQL Server in your Zones. If you are using SQL Server, please confirm that you are using at least SQL Server 2005 or all the well intentioned suggestions using ROW_NUMBER() OVER (ORDER BY ... are not going to help you very much.
sql syntax help -sybase
however you added MS SQL Server in your Zones. If you are using SQL Server, please confirm that you are using at least SQL Server 2005 or all the well intentioned suggestions using ROW_NUMBER() OVER (ORDER BY ... are not going to help you very much.
Try this one out:
select *
from (
select distinct a15.title title, a11.author author, a14.processed processed, a12.creation_date creation_day, a13.collection collection, a16.descr descr, row_number() over (order by a12.creation_date ) as rnum
from rv_bib_author a11, rv_bib_control a12, rv_item a13, rv_isbn a14, rv_title_inverted a15, collection a16 where a11.bib_id = a12.bib_id and a11.bib_id = a13.bib_id and a11.bib_id = a14.bib_id and a11.bib_id = a15.bib_id and a13.collection = a16.collection and (a13.collection in ('f', 'nf')and (a14.processed IS NOT NULL) and a12.creation_date between '03/01/2009' and '05/30/2019') order by a12.creation_date desc ) as temp
where rnum = 1
ASKER
@acperkins:I need it only on sybase...
@rrjegan17:Msg 1033, Level 15, State 1, Line 5
The ORDER BY clause is invalid in views, inline functions, derived tables, subqueries, and common table expressions, unless TOP or FOR XML is also specified.
@rrjegan17:Msg 1033, Level 15, State 1, Line 5
The ORDER BY clause is invalid in views, inline functions, derived tables, subqueries, and common table expressions, unless TOP or FOR XML is also specified.
Did you not see the solution provided by ralmada http:#a25106497 ? It does not have the syntax error with the ORDER BY clause in the derived table.
Thank you AC.
@jack,
basically the problem with rrjegan solution is that it has an order by in the subquery.
select * from (
select .... from
where .....
order by ... -- THIS IS WRONG.
) a
The above is incorrect. you need to move the order by clause outside of the subquery.
select * from (
select .... from
where .....
) a
order by ... -- THIS IS CORRECT
that's what I've suggested in my previous comment 25106497.
@jack,
basically the problem with rrjegan solution is that it has an order by in the subquery.
select * from (
select .... from
where .....
order by ... -- THIS IS WRONG.
) a
The above is incorrect. you need to move the order by clause outside of the subquery.
select * from (
select .... from
where .....
) a
order by ... -- THIS IS CORRECT
that's what I've suggested in my previous comment 25106497.
And please don't dom't get suckered into doing this cheesy workaround:
select * from (
select TOP 100 PERCENT ...
from ...
where .....
order by ... ) a
select * from (
select TOP 100 PERCENT ...
from ...
where .....
order by ... ) a
ASKER
@ralmada :I tried ur solution...it doesnt show any error.But also it doesnt retrieve any record.
ASKER
@ralmada : oops sorry ur query i working ...When i have changed the values.But see it gets wrong records...
title1 author1 1234 2009-01-08 00:00:00.000 f this is a movie 1
title1 author1 4321 2009-01-08 00:00:00.000 f this is a movie 1
title3 author3 8765 2009-01-08 00:00:00.000 song this is a song 1
title2 author2 5678 2009-02-08 00:00:00.000 f this is a movie 1
title1 author1 1234 2009-01-08 00:00:00.000 f this is a movie 1
title1 author1 4321 2009-01-08 00:00:00.000 f this is a movie 1
title3 author3 8765 2009-01-08 00:00:00.000 song this is a song 1
title2 author2 5678 2009-02-08 00:00:00.000 f this is a movie 1
so you just want to return records with unique title1 right? So try this:
select *
from (
select distinct a15.title title,
a11.author author,
a14.processed processed,
a12.creation_date creation_day,
a13.collection collection,
a16.descr descr,
row_number() over (partition by a15.title order by a12.creation_date) as rnum
from rv_bib_author a11,
rv_bib_control a12,
rv_item a13,
rv_isbn a14,
rv_title_inverted a15,
collection a16
where a11.bib_id = a12.bib_id and
a11.bib_id = a13.bib_id and
a11.bib_id = a14.bib_id and
a11.bib_id = a15.bib_id and
a13.collection = a16.collection and
(a13.collection in ('f', 'nf')and
(a14.processed IS NOT NULL) and
a12.creation_date between '03/01/2009' and '05/30/2019')
) as temp
where rnum = 1
order by creation_day desc
ASKER
Ok i will check ur solution.
1.One quick question, Is it wrong to use the order by clause in the sub query?
>>>row_number() over (partition by a15.title order by a12.creation_date)
2.Also here we should use a orderby clause?
3.Also partition by means like group by?
4.why not we use rank() function here ,how it is different from the row_number().
Thanks for all ur time.
1.One quick question, Is it wrong to use the order by clause in the sub query?
>>>row_number() over (partition by a15.title order by a12.creation_date)
2.Also here we should use a orderby clause?
3.Also partition by means like group by?
4.why not we use rank() function here ,how it is different from the row_number().
Thanks for all ur time.
As ralmada and acperkins pointed out, order by clause in the query provided by me earlier should be removed..
Hence it should be like
Also partition by clause needs to be removed for Sybase:
Hence it should be like
Also partition by clause needs to be removed for Sybase:
select *
from (
select DISTINCT a15.title title, a11.author author, a14.processed processed, a12.creation_date creation_day, a13.collection collection, a16.descr descr, row_number() over (order by a12.creation_date ) rnum
from rv_bib_author a11, rv_bib_control a12, rv_item a13, rv_isbn a14, rv_title_inverted a15, collection a16 where a11.bib_id = a12.bib_id and a11.bib_id = a13.bib_id and a11.bib_id = a14.bib_id and a11.bib_id = a15.bib_id and a13.collection = a16.collection and (a13.collection in ('f', 'nf')and (a14.processed IS NOT NULL) and a12.creation_date between '03/01/2009' and '05/30/2019')) as temp
where rnum = 1
ASKER
I should use the title in the order by clause or creation date?
>> I should use the title in the order by clause or creation date?
Can you kindly explain more on what you referred by this.
Can you kindly explain more on what you referred by this.
Another alternative would be doing something like this:
select distinct a15.title title,
a11.author author,
a14.processed processed,
a12.creation_date creation_day,
a13.collection collection,
a16.descr descr,
from rv_bib_author a11,
rv_bib_control a12,
rv_item a13,
(select bib_id, min(processed) processed from rv_isbn group by bib_id) a14,
rv_title_inverted a15,
collection a16
where a11.bib_id = a12.bib_id and
a11.bib_id = a13.bib_id and
a11.bib_id = a14.bib_id and
a11.bib_id = a15.bib_id and
a13.collection = a16.collection and
(a13.collection in ('f', 'nf')and
(a14.processed IS NOT NULL) and
a12.creation_date between '03/01/2009' and '05/30/2019')
order by a12.creation_date descASKER
@ralmada: In sybase i tried ur first solution:
sybase_query() [function.sybase-query]: message: Incorrect syntax near the keyword 'over'. (severity 15)
second one giving some 4 errors...
sybase_query() [function.sybase-query]: message: Incorrect syntax near the keyword 'over'. (severity 15)
second one giving some 4 errors...
Well, for the second one, there was an extra comma there:
select distinct a15.title title,
a11.author author,
a14.processed processed,
a12.creation_date creation_day,
a13.collection collection,
a16.descr descr
from rv_bib_author a11,
rv_bib_control a12,
rv_item a13,
(select bib_id, min(processed) processed from rv_isbn group by bib_id) a14,
rv_title_inverted a15,
collection a16
where a11.bib_id = a12.bib_id and
a11.bib_id = a13.bib_id and
a11.bib_id = a14.bib_id and
a11.bib_id = a15.bib_id and
a13.collection = a16.collection and
(a13.collection in ('f', 'nf')and
(a14.processed IS NOT NULL) and
a12.creation_date between '03/01/2009' and '05/30/2019')
order by a12.creation_date desc
ASKER
This is working great for me in mssql(sybase i dont have so i tried there).
The same query i am uing in magento too.
can u get me a mysql query?
The same query i am uing in magento too.
can u get me a mysql query?
>>mysql query? <<
It should be the same syntax.
It should be the same syntax.
ASKER
I took out the comma after a16.descr descr and ran the query again- no errors, but no data returned
hmmm, try like this:
select distinct a15.title title,
a11.author author,
a14.processed processed,
a12.creation_date creation_day,
a13.collection collection,
a16.descr descr
from rv_bib_author a11,
rv_bib_control a12,
rv_item a13,
(select bib_id, min(processed) processed from rv_isbn where processed is not null group by bib_id) a14,
rv_title_inverted a15,
collection a16
where a11.bib_id = a12.bib_id and
a11.bib_id = a13.bib_id and
a11.bib_id = a14.bib_id and
a11.bib_id = a15.bib_id and
a13.collection = a16.collection and
(a13.collection in ('f', 'nf')and
a12.creation_date between '03/01/2009' and '05/30/2019')
order by a12.creation_date descASKER
sorry again.
No record found...
No record found...
Can you run this and see what you get?
select bib_id, min(processed) processed from rv_isbn where processed is not null group by bib_id
ASKER
1 1234
2 5678
3 4321
4 8765
2 5678
3 4321
4 8765
ASKER
U can also the results here...
the working code displays duplicates, which is what I'm trying to fix...
http://www.myrcpl.com/onorder.php
the working code displays duplicates, which is what I'm trying to fix...
http://www.myrcpl.com/onorder.php
ASKER
ASKER CERTIFIED SOLUTION
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
ASKER
ok i will try and let u know.
ASKER
This is working fine...
Can u please explain what is the difference between the queries u gave before and this one.
Can u please explain what is the difference between the queries u gave before and this one.
ASKER
@ralmada:
Thank u so much for ur help.
@rrjegan,@acperkinsi :
Thanks u too for ur time
.I really enjoyed here in EE by learning with the experts around the globe .
Thank u so much for ur help.
@rrjegan,@acperkinsi :
Thanks u too for ur time
.I really enjoyed here in EE by learning with the experts around the globe .
I basically removed the duplicates from table rv_isbn using this part in line 21.
a14.processed = (select min(processed) from rv_isbn where bib_id = a11.bib_id)
a14.processed = (select min(processed) from rv_isbn where bib_id = a11.bib_id)
>>Thank u so much for ur help.<<
Excellent! Now please close the question.
Excellent! Now please close the question.
ASKER
Thank u so much...