sql server - Select Max Limit 1 from Group -

-

i'm making in webpage cache system. wanted make simple page rank system along output. problem is, want display recordset highest relevance score per unique domain. 1 domain may have multiple records different titles, descriptions, etc. problem is, instead of getting 1 recordset containing unique domain, groups recordsets of unique domain , outputs them all. want recordset highest relevance score per unique domain per group before outputs next (and different domain highest relevance group)

select title, html, sum(relevance)   (   select title, html, 10 relevance page title ‘%about%’ union   select title, html, 7 relevance page html ‘%about%’ union   select title, html, 5 relevance page keywords ‘%about%’ union   select title, html, 2 relevance page description ‘%about%’   ) results group title, html order relevance desc;

i'm getting:

domain1 title html domain1 title html domain1 title html domain2 title html domain2 title html domain2 title html

what want 

domain1 title html domain2 title html domain3 title html domain4 title html domain5 title html

i'm not sure why code works, since think should have

 order sum(relevance) desc

instead of

 order relevance desc

maybe that's problem?

beyond that, this. ugly, work. better if sql server understood how refer aliases later in query. alas.

 select title, html, case when title '%about%' 10 else 0 end + case when html '%about%' 7 else 0 end + case when keywords '%about%' 5 else 0 end + case when description '%about%' 2 else 0 end relevance page case when title '%about%' 10 else 0 end + case when html '%about%' 7 else 0 end + case when keywords '%about%' 5 else 0 end + case when description '%about%' 2 else 0 end > 0 order case when title '%about%' 10 else 0 end + case when html '%about%' 7 else 0 end + case when keywords '%about%' 5 else 0 end + case when description '%about%' 2 else 0 end desc;

or maybe slight rearrangement:

 select title, html, relevance (select title, html, case when title '%about%' 10 else 0 end + case when html '%about%' 7 else 0 end + case when keywords '%about%' 5 else 0 end + case when description '%about%' 2 else 0 end relevance page) relevance > 0 order relevance desc;

Comments

Post a Comment

Popular posts from this blog

c++ - How do I get a multi line tooltip in MFC -

-
right now, have tool tip pops when hover on edit box. problem tool tip contains multiple error messages , in 1 long line. need have each error message on own line. error messages contained in cstring new line seperating them. my existing code below. bool ontooltiptext(uint, nmhdr* pnmhdr, lresult* presult) { assert(pnmhdr->code == ttn_needtexta || pnmhdr->code == ttn_needtextw); // need handle both ansi , unicode versions of message tooltiptexta* pttta = (tooltiptexta*)pnmhdr; tooltiptextw* ptttw = (tooltiptextw*)pnmhdr; // tchar szfulltext[256]; cstring strtiptext=_t(""); uint nid = pnmhdr->idfrom; if (pnmhdr->code == ttn_needtexta && (pttta->uflags & ttf_idishwnd) || pnmhdr->code == ttn_needtextw && (ptttw->uflags & ttf_idishwnd)) { // idfrom hwnd of tool nid = ::getdlgctrlid((hwnd)nid); } //m_errprojaccel[ch] contains 1 or more error messages each...
Read more

asp.net - In javascript how to find the height and width -

-
i having .aspx , masterpage page. in masterpage loading aspx page . in aspx page design using generating table structure using code-behind server in javascript how find height , width . regards, kumar if you're looking table height , width, should it var tbl = document.getelementbyid('tbldemo'); alert(tbl.offsetwidth + '\n' + tbl.offsetheight);
Read more

c# - DataTable to EnumerableRowCollection -

-
i have datatable according client requirement can contain invalid date "00/00/1999",null,empty string.(typed datset) when collect them enumerables wish convert of invalid forms empty string. (i.e) enumerablerowcollection<datarow> query = order in _table.asenumerable() select new { orderdate= string.isnullorempty(convert.tostring(order.field<datetime>("orderdate")) || (how check date invalid date) ? string.empty : order.field<datetime>("orderdate") } how check whether date valid date or not? datetime.tryparse...like given current code: datetime date; enumerablerowcollection<datarow> query = order in _table.asenumerable() select new { orderdate= string.isnullorempty(convert.tostring(order.field<datetime>("orderdate")) || !datetime.tryparse(convert.tostring(order.field<datetime...
Read more