xeondxb
asked on
Oracle 10g query Need Help!
Im fetching books or publication from oracle 10g but issue is author profile could be 2, 3 or more if I need 10 record so it could be 13 or 14 it depend on author some books may have only one author and some books may have 2 or three I need 10 records of books with 10 records maybe authors could be more then 10 please have a look my query what im writing in this query im getting 10 records but im losing some books coz, of 10 limit …
select * from (
SELECT
USA_BOOKS.PUB_ID id,
USA_BOOKS.DETAIL_FILE_URL detailFileURL,
USA_BOOKS.PUB_FIRST_EDN_YE AR editionYear ,
USA_BOOKS.TITLE,
USA_BOOKS.WHAT_IS_NEW_IMAG E WHATISNEWIMAGE,
PRF.PRF_ID AS prfId,
PRF.PRF_NAME AS prfName,
PRF.PRF_IS_CLICKABLE AS prfIsClickable,
profiles.PRF_TYPE prfType
FROM
USA_BOOKS_en USA_BOOKS,
USA_BOOKS_PROFILES_en profiles,
PROFILE_en PRF
WHERE
(USA_BOOKS.IS_USA LIKE '1' ||'%' OR USA_BOOKS.IS_USA LIKE '0%') AND
USA_BOOKS.PUB_ID = profiles.PUB_ID AND
PRF.PRF_ID = profiles.PRF_ID
ORDER BY TO_DATE(USA_BOOKS.PUB_POST ED_DATE, 'MM dd yyyy') DESC , USA_BOOKS.PUB_ID ASC, profiles.PRF_TYPE ASC
)
where rownum <11
select * from (
SELECT
USA_BOOKS.PUB_ID id,
USA_BOOKS.DETAIL_FILE_URL detailFileURL,
USA_BOOKS.PUB_FIRST_EDN_YE
USA_BOOKS.TITLE,
USA_BOOKS.WHAT_IS_NEW_IMAG
PRF.PRF_ID AS prfId,
PRF.PRF_NAME AS prfName,
PRF.PRF_IS_CLICKABLE AS prfIsClickable,
profiles.PRF_TYPE prfType
FROM
USA_BOOKS_en USA_BOOKS,
USA_BOOKS_PROFILES_en profiles,
PROFILE_en PRF
WHERE
(USA_BOOKS.IS_USA LIKE '1' ||'%' OR USA_BOOKS.IS_USA LIKE '0%') AND
USA_BOOKS.PUB_ID = profiles.PUB_ID AND
PRF.PRF_ID = profiles.PRF_ID
ORDER BY TO_DATE(USA_BOOKS.PUB_POST
)
where rownum <11
ASKER CERTIFIED SOLUTION
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
Can you explain with some sample data on what exactly are you looking for?
ASKER
if you check highlighted records you will see there is 2 authors and im not using
SELECT
USA_BOOKS.PUB_ID id,
USA_BOOKS.DETAIL_FILE_URL detailFileURL,
USA_BOOKS.PUB_FIRST_EDN_YEAR editionYear ,
USA_BOOKS.TITLE,
USA_BOOKS.WHAT_IS_NEW_IMAGE WHATISNEWIMAGE,
PRF.PRF_ID AS prfId,
PRF.PRF_NAME AS prfName,
PRF.PRF_IS_CLICKABLE AS prfIsClickable,
profiles.PRF_TYPE prfType
FROM
USA_BOOKS_en USA_BOOKS,
USA_BOOKS_PROFILES_en profiles,
PROFILE_en PRF
WHERE
(USA_BOOKS.IS_USA LIKE '1' ||'%' OR USA_BOOKS.IS_USA LIKE '0%') AND
USA_BOOKS.PUB_ID = profiles.PUB_ID AND
PRF.PRF_ID = profiles.PRF_ID
ORDER BY TO_DATE(USA_BOOKS.PUB_POSTED_DATE, 'MM dd yyyy') DESC , USA_BOOKS.PUB_ID ASC, profiles.PRF_TYPE ASC
screen.jpg
ASKER
@MrNed: i tried your given query it is giving me only 2 results :(
OK, not sure why. Do you understand what I am trying to do with my query? It's not clear what you want, I'm assuming you want 10 distinct books, each with one row per author. So the final output will be at least 10 rows, maybe more.
ASKER
@MrNed:
10 books records but one book author can be more then 1 so records will be more then 10 which book have more then one author for example "AB book" have 2 author 1:XY 2:YZ except this book all books written by one author so record will be 11 because of "AB book" because its duplicating ...
10 books records but one book author can be more then 1 so records will be more then 10 which book have more then one author for example "AB book" have 2 author 1:XY 2:YZ except this book all books written by one author so record will be 11 because of "AB book" because its duplicating ...
So what does the inner part of my query give you:
select * from (
SELECT
USA_BOOKS.PUB_ID id,
USA_BOOKS.DETAIL_FILE_URL detailFileURL,
USA_BOOKS.PUB_FIRST_EDN_YE AR editionYear ,
USA_BOOKS.TITLE,
USA_BOOKS.WHAT_IS_NEW_IMAG E WHATISNEWIMAGE,
USA_BOOKS.IS_USA
FROM
USA_BOOKS_en USA_BOOKS
ORDER BY TO_DATE(USA_BOOKS.PUB_POST ED_DATE, 'MM dd yyyy') DESC , USA_BOOKS.PUB_ID ASC
)
where rownum <11
select * from (
SELECT
USA_BOOKS.PUB_ID id,
USA_BOOKS.DETAIL_FILE_URL detailFileURL,
USA_BOOKS.PUB_FIRST_EDN_YE
USA_BOOKS.TITLE,
USA_BOOKS.WHAT_IS_NEW_IMAG
USA_BOOKS.IS_USA
FROM
USA_BOOKS_en USA_BOOKS
ORDER BY TO_DATE(USA_BOOKS.PUB_POST
)
where rownum <11
ASKER
SELECT
USA_BOOKS.PUB_ID id,
USA_BOOKS.DETAIL_FILE_URL detailFileURL,
USA_BOOKS.PUB_FIRST_EDN_YE AR editionYear ,
USA_BOOKS.TITLE,
USA_BOOKS.WHAT_IS_NEW_IMAG E WHATISNEWIMAGE,
PRF.PRF_ID AS prfId,
PRF.PRF_NAME AS prfName,
PRF.PRF_IS_CLICKABLE AS prfIsClickable,
profiles.PRF_TYPE prfType
FROM
USA_BOOKS_en USA_BOOKS,
USA_BOOKS_PROFILES_en profiles,
PROFILE_en PRF
WHERE
(USA_BOOKS.IS_USA LIKE '1' ||'%' OR USA_BOOKS.IS_USA LIKE '0%') AND
USA_BOOKS.PUB_ID = profiles.PUB_ID AND
PRF.PRF_ID = profiles.PRF_ID
ORDER BY TO_DATE(USA_BOOKS.PUB_POST ED_DATE, 'MM dd yyyy') DESC , USA_BOOKS.PUB_ID ASC, profiles.PRF_TYPE ASC
when im writing this im getting result which u can see screen shoot above from that result i want 10 records but as i told u the condition 10 records should be books but it'll not apply on authors 10 books but if more then one author of one book it could be 12 records or 11 records because of author record
USA_BOOKS.PUB_ID id,
USA_BOOKS.DETAIL_FILE_URL detailFileURL,
USA_BOOKS.PUB_FIRST_EDN_YE
USA_BOOKS.TITLE,
USA_BOOKS.WHAT_IS_NEW_IMAG
PRF.PRF_ID AS prfId,
PRF.PRF_NAME AS prfName,
PRF.PRF_IS_CLICKABLE AS prfIsClickable,
profiles.PRF_TYPE prfType
FROM
USA_BOOKS_en USA_BOOKS,
USA_BOOKS_PROFILES_en profiles,
PROFILE_en PRF
WHERE
(USA_BOOKS.IS_USA LIKE '1' ||'%' OR USA_BOOKS.IS_USA LIKE '0%') AND
USA_BOOKS.PUB_ID = profiles.PUB_ID AND
PRF.PRF_ID = profiles.PRF_ID
ORDER BY TO_DATE(USA_BOOKS.PUB_POST
when im writing this im getting result which u can see screen shoot above from that result i want 10 records but as i told u the condition 10 records should be books but it'll not apply on authors 10 books but if more then one author of one book it could be 12 records or 11 records because of author record
SOLUTION
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
ASKER
it did not work but thanks guys.... just closing question