Tag Info

New answers tagged

0

As the EXPLAIN SELECT count(id_b), id_c FROM A WHERE id_b IN([concated_list]) GROUP BY id_c shows, the reason for the slow SELECT is that the id_b INDEX is not being used. Replacing the id_b INDEX with a (id_b,id_c) INDEX solved the problem and reduced the query time by 99% from 1-2s to 10-20ms depending on the specific c_id. Thank you so much @ everyone ...


1

EDIT: Since the first try with a subselect lead into doom, it might be necessary to split the table into a hot range and a "cold" range. That solution only could help if you have some criteria for id_c and id_b, which shows if this id_c will be read often or rarely, for example the age of this. But even deleting from such a big table is not so easy, it would ...


0

Please try this query: select * from PROFILES where SCORING_RULE = 'double_scoring' and ROWNUM <= 6 union select * from PROFILES where SCORING_RULE = 'single_scoring' and ROWNUM <= 4 Check it here: http://sqlfiddle.com/#!4/7ecc95/1


2

Based on your comments, here is a maybe faster version: SELECT TABLE1.* , TABLE2.COL1 , TABLE2.COL4 FROM TABLE1 INNER JOIN TABLE2 ON TABLE1.COL4 = TABLE2.COL2 WHERE TABLE1.COL1 IN ('1','TEXT','HO TED') AND TABLE1.COL2=1800 AND TABLE1.COL3>=100 AND TABLE2.COL1=10519198 AND TABLE2.COL3=('TEXT') And you need to create a tree-index on ...


5

In Postgres, this is simpler with DISTINCT ON: SELECT * FROM ( SELECT DISTINCT ON (sec) id, sec FROM tbl ORDER BY sec, id DESC ) sub ORDER BY id DESC LIMIT 4; Detailed explanation in this related answer on SO: Select first row in each GROUP BY group? For a big table and small LIMIT, neither this nor @a_horse's solution are very ...


0

In the absence of any set word for this behaviour, may I suggest it should be called: SELECSERT


5

SELECT id, sec FROM ( SELECT id, sec, row_number() OVER (PARTITION BY sec ORDER BY id DESC) AS rn FROM my_table ) t WHERE rn = 1 ORDER BY id DESC LIMIT 4; SQLFiddle example: http://sqlfiddle.com/#!15/1ca01/1


1

I assume you're talking about Relational Algebra here. A SELECT operation returns whole tuples (rows) based on some predicate. The predicate is defined in the SQL statement by the WHERE clause. The PROJECT algebra operation is where you select a subset of attributes from each tuple. The PROJECT operation is defined in the SQL statement by the columns ...


2

For Access you can use: WHERE obs LIKE '*[#]*' There are three wildcard characters in Access that can be used with LIKE operator: `*` for a string of arbitrary length (equivalent ANSI: `%`) `?` for a single character (equivalent ANSI: `_`) `#` for a single numeric digit `[` for escaping If you want to search for one of those wildcards, you have to ...


1

Just replace the '*' with '%' as SELECT id, obs FROM Person WHERE obs Like '%#%'


1

To only check the occurrence of '#' you can use LOCATE() SELECT id, obs FROM Person WHERE LOCATE('#',obs) >0 Fiddle Demo For you access query SELECT id, obs FROM Person WHERE obs Like '*[#]*'


1

You should return something when nothing is found. Before exception comment in the statement. The way your routine is written it may does find nothing and then hits the end of the function without returning anything, hence the error function returned without value. Workaround : Handle it with the exception exception when TOO_MANY_ROWS then return ...


3

The query to accomplish this would be of the form: SELECT t.UniqueKey FROM mytable t WHERE t.UniqueKey = ? AND t.timeStamp >= NOW() - INTERVAL 5 MINUTE AND t.timeStamp <= NOW() LIMIT 1 (This query assumes that the timeStamp column is defined as datatype TIMESTAMP.) The query will either return one or zero rows, which will indicate either ...


1

For the table mydb.mytable with UniqueKey and timeStamp, to see if the UniqueKey exists within the last 5 minutes, simply run this SELECT COUNT(1) FROM mydb.mytable WHERE UniqueKey = ???? AND timeStamp >= ( NOW() - INTERVAL 5 MINUTE ); or SELECT COUNT(1) FROM mydb.mytable WHERE UniqueKey = ???? AND timeStamp >= ( NOW() - INTERVAL 300 SECOND ); ...



Top 50 recent answers are included