Tag Info

New answers tagged

2

I'm not sure I fully understand what you expect to happen with the SQL you pasted .. (add up the numbers from 1-99 ignoring all even values?) But this is a SQL to get around the syntax error you have: WITH RECURSIVE t(n) AS ( VALUES (1) UNION ALL SELECT n+1 FROM t WHERE n+1 not in (t.n) and n < 100 ) SELECT sum(n) FROM t; I would like to ...


1

A possible Solution to the Problem I found working is a step-by-step solution which looks like this: BEGIN; CREATE TEMP TABLE route ON COMMIT DROP AS SELECT seq, source, target, km, kmh, clazz, geom_way FROM pgr_dijkstra('SELECT id, source, target, cost FROM de_2po_4pgr, (SELECT ST_Expand(ST_Extent(geom_vertex),0.1) as box FROM de_2po_vertex ...


2

Yes, it is possible to return both results without repeating the subquery by rewriting your query using an outer join. The following will return same results as your version: SELECT COUNT(*) AS `count` FROM agreements AS ag LEFT JOIN ( SELECT a.driv_id, a.veh_id FROM agreements a INNER JOIN ...


0

If you need to combine result sets from different tables, use the UNION operator with two queries. Since the sets have different and unrelated column names, you will need to select a placeholder (usually NULL) in each query for columns in the other query: select `title`,`txt`,`img`, NULL, NULL, NULL, NULL, NULL, NULL from `content_m2m` inner join `content` ...


4

The feature of Postgres to be able to use the primary key of a table with GROUP BY and not need to add the other columns of that table in the GROUP BY clause is relatively new and works only for base tables. The optimizer is not (yet?) clever enough to identify primary keys for views, ctes or derived tables (as in your case). You can add the columns you ...


0

If you had an indexed date dimension table, this would work much faster. They are quite useful for solving a number of reporting issues. A very simple example (your dimension can be as robust or simple as you require) would be: CREATE TABLE month_dimension (Month INT, Year INT, First_Day DATE) INSERT INTO month_dimension VALUES (1, 2015, '1/1/2015'), (2, ...


0

You can use join on the same table. Its called self join. Like: FROM bookings b INNER JOIN bookings book2 ON b.columnname = book2.columnname



Top 50 recent answers are included