Finding Median with PostgreSql

This is how you can get the median value of a column in PostgreSql. Thanks to http://old.nabble.com/SELECT-TOP–d-PERCENT,-or-SELECT-…-LIMIT–d-PERCENT—td19941366.html

 

WITH c AS (select count(*)/2 as n from FOO) select COL from FOO order by COL asc limit (1) offset (select n from c);

 

It is much simpler if you are using SQL Server as described in: http://www.sqlmag.com/Article/ArticleID/49827/sql_server_49827.html

SELECT MAX(Value) FROM
  (SELECT TOP 50 PERCENT Value FROM dbo.VOrders ORDER BY Value) AS H1;

--
Reza++

SubQuery

You can only do correlate subqueries in SELECT and WHERE clauses.

So, a subquery in FROM clause does not have access to the outer select.

select 	"Damage" , "IdAttackerWeapon" as w,
	(select sum(dd)
	from
		(select "Damage", "IdAttackerWeapon", abs(("Damage" - t."Damage")) as dd
		from fact_eventplayerdamage order by dd asc limit 5)
	t1)
from fact_eventplayerdamage t
group by "Damage", w

—

Reza

Unicode with Lucene

If your Unicode file cannot be found by Lucene, try indexing the file in this way too.

	InputStreamReader( new FileInputStream(filename), "UTF-8");

—

REZA++

Starting Lucene

If you wanna start working with Lucene, I really recommend you to read Lucene Tutorial first. Thanks Steven J. Owens for putting it there.

Some notes from the tutorial:

• Lucene is a high-performance, full-featured, java, open-source, text search engine API written by Doug Cutting.
• you must make sure you use the same sort of analyzer for indexing and for searching
• Don’t Get Clever
• Remember Knuth: “early optimization is the root of much evil.”
• A minimum, as in the standard Lucene examples, would be:
  

  A field containing…	Which you’ll use to…
  the path to the original document	actually show the user the original document after the search
  a modification date	compare against the original Document’s modification date, to see if it needs to be reindexed.
  the contents of the file	run the search against

• Indexing and searching are not only thread safe, but process safe
• The query parser is not thread safe, so each thread using the index should have its own query parser.
• The index writer however, is thread safe, so you can update the index while people are searching it.
• Make sure that the threads with open index searchers close them and open new ones, to get the newly updated data.
• Fortunately, Lucene makes this really, really easy, because most – or all – of the key Lucene classes are thread-safe.

—

REZA++