Have Geek Brain

PostgreSQL if request?

Is there a way to select records based on using an if statement?

My table looks like this:

id | num | dis 
1  | 4   | 0.5234333
2  | 4   | 8.2234
3  | 8   | 2.3325
4  | 8   | 1.4553
5  | 4   | 3.43324

And I want to select num

and dis

, where dis

is the lowest number ... So, a query that will give the following results:

id | num | dis 
1  | 4   | 0.5234333
4  | 8   | 1.4553
+2


a source to share


3 answers


If you want all rows with a minimum value inside a group:

SELECT id, num, dis
FROM table1 T1
WHERE dis = (SELECT MIN(dis) FROM table1 T2 WHERE T1.num = T2.num)

Or you can use a join to get the same result:

SELECT T1.id, T1.num, T1.dis
FROM table1 T1
JOIN (
    SELECT num, MIN(dis) AS dis
    FROM table1
    GROUP BY num
) T2
ON T1.num = T2.num AND T1.dis = T2.dis


If you only need one line from each group, even if there are links, you can use this:

SELECT id, dis, num FROM (
    SELECT id, dis, num, ROW_NUMBER() OVER (PARTITION BY num ORDER BY dis) rn
    FROM table1
) T1
WHERE rn = 1

Unfortunately, this will not be very effective. If you need something more efficient, check out Quassnoi 's page on Selecting Max Grouped Rows for PostgreSQL . Here he suggests several ways to accomplish this query and explains the effectiveness of each. The summary from the article looks like this:

Unlike MySQL, PostgreSQL implements several clean and documented ways to select records that contain group maximum values, including window functions and DISTINCT ON.

However, due to the lack of a free index, the PostgreSQL optimizer scan support and the less efficient use of indexes in PostgreSQL, queries using these functions take too long.

To work around these problems and improve queries against low cardinality grouping conditions, the specific solution that is described in the article should be used.

This solution uses recursive CTEs to emulate index unwrapping and is very efficient if the grouping columns are low cardinality.

+4


a source


Use this:

SELECT DISTINCT ON (num) id, num, dis
FROM tbl
ORDER BY num, dis


Or if you are going to use other RDBMSs in the future, use this:

select * from tbl a where dis =
(select min(dis) from tbl b where b.num = a.num)
+1


a source


If you need IF logic, you can use PL / pgSQL.

http://www.postgresql.org/docs/8.4/interactive/plpgsql-control-structures.html

But try to fix the problem with SQL first if possible, it will be faster and will use PL / pgSQL when SQL fails to solve your problem.

0


a source






More articles:


All Articles