Tag Info

New answers tagged

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 ); ...


0

With the restrictions you have supplied and if you want to implement this is DBMS, I think you could use a supertype/subtype pattern for the 5 (or more) types of objects and only one additional table for the "object contains objects" list: -- auxiliary table that has only 5 rows, one for each type CREATE TABLE types ( level TINYINT NOT NULL PRIMARY KEY , ...



Top 50 recent answers are included