SQL Query [message #626836] |
Tue, 04 November 2014 01:51 |
Raakh
Messages: 12 Registered: December 2009
|
Junior Member |
|
|
Hello,
Oracle Database 11g Enterprise Edition Release 11.2.0.1.0 - 64bit Production
With the Partitioning, OLAP, Data Mining and Real Application Testing options
SQL> desc news
Name Null? Type
----------------------------------------- -------- ----------------------------
NEWS_ID NOT NULL NUMBER(14)
NEWS_DATE TIMESTAMP(0)
SL_ID NUMBER(2)
HEADING VARCHAR2(3120)
DESCRIPTION VARCHAR2(3900)
I am working with news slider and I intend to select 5 news randomnly from:
1) Last posted 15 news_ids
2) 50 words will be selected from description
3) If in these 50 words "<table" or "<img" or "<br>" exist then replace the first backet of the tag like "<!"
4) This process will be repeated auto after every 3 hours
Please advise
Thanks in aniticpation
|
|
|
Re: SQL Query [message #626840 is a reply to message #626836] |
Tue, 04 November 2014 01:55 |
Lalit Kumar B
Messages: 3174 Registered: May 2013 Location: World Wide on the Web
|
Senior Member |
|
|
Please provide the create and insert statements as sample data. You need to provide a test case.
Raakh wrote on Tue, 04 November 2014 13:21
I am working with news slider and I intend to select 5 news randomnly from:
1) Last posted 15 news_ids
2) 50 words will be selected from description
3) If in these 50 words "<table" or "<img" or "<br>" exist then replace the first backet of the tag like "<!"
4) This process will be repeated auto after every 3 hours
1. For random Rows, use ROWNUM <=15.
2. Use SUBSTR.
3. REGEXP_REPLACE perhaps, but solution depends on your data.
4. DBMS_SCHEDULER.
[Updated on: Tue, 04 November 2014 01:59] Report message to a moderator
|
|
|
|
|
|
|
|
Re: SQL Query [message #626869 is a reply to message #626861] |
Tue, 04 November 2014 04:14 |
Lalit Kumar B
Messages: 3174 Registered: May 2013 Location: World Wide on the Web
|
Senior Member |
|
|
Michel Cadot wrote on Tue, 04 November 2014 15:26
Quote:OP says he needs last posted 15 news_id
As far I understand the point 1, he needs 5 random rows among the 15 newest ones.
My understanding is also the same. The latest 15 news_id could be top 15 rows after sorting them in descending order. And, then the 5 random rows among them.
@OP, but, why 5 random rows out of the 15 selected, better select the top 5 rows at the first place.
|
|
|
Re: SQL Query [message #626998 is a reply to message #626840] |
Thu, 06 November 2014 07:10 |
Raakh
Messages: 12 Registered: December 2009
|
Junior Member |
|
|
Hello,
Sorry for the late reply as I was travelling
Quote:2. Use SUBSTR.
Its finding and displaying subStrings whereas I am trying to display words
Quote:3. REGEXP_REPLACE perhaps, but solution depends on your data.
I am trying to set it in my Java code
Quote:4. DBMS_SCHEDULER.
Good advice. I am checking some example
Thanks & best regards
|
|
|
Re: SQL Query [message #627000 is a reply to message #626998] |
Thu, 06 November 2014 07:44 |
Lalit Kumar B
Messages: 3174 Registered: May 2013 Location: World Wide on the Web
|
Senior Member |
|
|
Your topic title is SQL QUERY. So, point 1,2 and 3 can be done with plain sql. You are yet to post the test case.
|
|
|
Re: SQL Query [message #627004 is a reply to message #627000] |
Thu, 06 November 2014 08:11 |
Raakh
Messages: 12 Registered: December 2009
|
Junior Member |
|
|
create table news(
news_id number(14) primary key,
news_Date timestamp(0) default sysdate,
heading varchar2(32),
description varchar2(320));
insert into news(heading, description) values('News heading','Description of the news will goes here. This is dynamic text that will be inserted');
the description may be different every time but I want to select say 5 first words e.g. "Description of the news will"
Thanks again
|
|
|
|
|
|
Re: SQL Query [message #627014 is a reply to message #627005] |
Thu, 06 November 2014 09:12 |
cookiemonster
Messages: 13920 Registered: September 2008 Location: Rainy Manchester
|
Senior Member |
|
|
Lalit Kumar B wrote on Thu, 06 November 2014 14:15>First 5 words
Substr is capable of doing that.
substr and instr together are capable of doing that.
|
|
|
|
|
|
Re: SQL Query [message #627046 is a reply to message #627044] |
Thu, 06 November 2014 14:42 |
Raakh
Messages: 12 Registered: December 2009
|
Junior Member |
|
|
Thanks for sending links of forum help. I presently getting words by using Java split() method but was looking to optimize performance if this is possible through oracle SQL but disappointed.
I searched a lot on google but was unable to find how to split words in sql by using substr and instr.
anyways still anxious to have any help in this regards
|
|
|
|
Re: SQL Query [message #627048 is a reply to message #627047] |
Thu, 06 November 2014 15:03 |
Raakh
Messages: 12 Registered: December 2009
|
Junior Member |
|
|
Thanks Michel Cadot for your provided link. I studied but not get any idea due to limited knowledge about oracle.
I am giving up at this point and realized oracle is very difficult to understand. I am feeling comfortable with Java split() function
Thanks all who have given me their precious time
Best regards
|
|
|
Re: SQL Query [message #627079 is a reply to message #627048] |
Fri, 07 November 2014 02:59 |
cookiemonster
Messages: 13920 Registered: September 2008 Location: Rainy Manchester
|
Senior Member |
|
|
You can use instr to search for spaces. Use that to find the position of the fourth space. then use that as a parameter to substr.
It's not complicated.
|
|
|
Re: SQL Query [message #627115 is a reply to message #627079] |
Fri, 07 November 2014 06:23 |
Raakh
Messages: 12 Registered: December 2009
|
Junior Member |
|
|
Hello,
public String singledOutNews(){
String news="";
try{
if ((cnn==null)||cnn.isClosed()){
cnn=DB.getOracleConnection();
}
String query = " select a.news_id,to_char(news_date,'Month day, yyyy'),heading,"+
" substr(description,1,370),country from news a, news_location b "+
" where a.news_id=b.news_id and rownum<5"+
" order by news_id desc";
PreparedStatement ps=cnn.prepareStatement(query);
rs=ps.executeQuery();
while(rs.next()){
String newsId =rs.getInt(1);
String newsDate =rs.getInt(2);
String heading =rs.getString(3);
String desc =rs.getString(4);
String country =rs.getString(5);
if(desc.equals("") || desc==null){
desc="";
}else{
desc=desc.replaceAll("<br>"," ");
int index=desc.lastIndexOf(" ");
String descByDroppingLastWord = desc.substring(0,index));
news+= "<a href=\"/servlet/displayNews1?newsID="+newsId+"\">"+
"<font size=3 color=olive face='times new roman'><b>"+
heading+"</b</font></a><br>"+
"<font color=teal size=1 face=verdana><b>"+rs.getString(2)+"</b></font>"+
"<font color=maroon size=1 face='verdana'> [<b>"+
country+"</b>] </b></font><br>"+descByDroppingLastWord+
"<a href=/servlet/displayNews?newsID="+news_id+"\"> Details.....</a><br><br>";
}
ps.close();
rs.close();
cnn.close();
}catch(Exception e){
errorMsg("ERROR: IndexDAO.displayNewsTicker()", e);
}
return news;
}
This is what I did with java code but still facing two problems:
1) To select rownum<=5 from out of last 15 rows
2) Repeating this query after every 3 hours
Best regards
|
|
|
Re: SQL Query [message #627120 is a reply to message #627115] |
Fri, 07 November 2014 06:42 |
Lalit Kumar B
Messages: 3174 Registered: May 2013 Location: World Wide on the Web
|
Senior Member |
|
|
Raakh wrote on Fri, 07 November 2014 17:53
String query = " select a.news_id,to_char(news_date,'Month day, yyyy'),heading,"+
" substr(description,1,370),country from news a, news_location b "+
" where a.news_id=b.news_id and rownum<5"+
" order by news_id desc";
That query will not return correct result set. ROWNUM is applied before ORDER BY and thus, you just get 5 random rows. You need to first sort the rows and then use ROWNUM in the subquery. In 12c, you can simply use TOP-n method using FETCH FIRST 5 ROWS ONLY. However, since you are on 11g, you could do it the way I explained.
As you initially said,
Quote:
I intend to select 5 news randomnly from:
1) Last posted 15 news_ids
I guess you need something like,
select * from(
select <column_list> from table
order by news_id desc
) where rownum <=15
Now, can you explain why do you again want 5 random rows from the above 15 rows? Why not select only 5 rows at first place.
[Updated on: Fri, 07 November 2014 06:43] Report message to a moderator
|
|
|
Re: SQL Query [message #627122 is a reply to message #627120] |
Fri, 07 November 2014 06:56 |
Raakh
Messages: 12 Registered: December 2009
|
Junior Member |
|
|
Thanks for replying
Daily 15 news are inserted in db. I intend to fetch data of 5 news randomnly to display in shape of 5 slides in websites. The query will run after each 3-4 hours to update these news slides/ticker to seem change in news website.
The following that you posted is selecting only 5 or 15 news in desc order not different 5 news from 15 everytime
select * from(
select news_id from news
order by news_id
) where rownum <=5;
Thanks again
|
|
|
Re: SQL Query [message #627123 is a reply to message #627122] |
Fri, 07 November 2014 07:49 |
cookiemonster
Messages: 13920 Registered: September 2008 Location: Rainy Manchester
|
Senior Member |
|
|
You'll need to randomize the order after getting the 15.
SELECT *
FROM (select *
from(select news_id
from news
ORDER by news_id DESC
)
where rownum <=15
ORDER BY dbms_random.value)
WHERE ROWNUM <= 5;
[Updated on: Fri, 07 November 2014 07:49] Report message to a moderator
|
|
|
Re: SQL Query [message #627124 is a reply to message #627123] |
Fri, 07 November 2014 07:50 |
cookiemonster
Messages: 13920 Registered: September 2008 Location: Rainy Manchester
|
Senior Member |
|
|
As for running the query every three hours, if you're coding this in java then that's a java issue, not a DB one.
|
|
|
|