Query to select first record in both PostgreSQL and SQL Server

Question

Query to select first record in both PostgreSQL and SQL Server

Navigation

#1 by (1 votes)
#2 by (0 votes)

0

I need to select the first record of a query. But the problem is that top 1 only works in SQL Server , but not in PostgreSQL . And in PostgreSQL it works with limit but in SQL Server no. These are the examples. I would greatly appreciate it if you know in any way how to generate this query for both types of databases.

PostgreSQL:

 SELECT c.crgo_id,c.crgo_nombre
            FROM workflow w 
            JOIN etapaworkflow ew ON w.wrko_id = ew.wrko_id 
            JOIN etapacargo ec ON ew.tpawrko_id = ec.tpawrko_id 
            JOIN cargo c ON c.crgo_id = ec.crgo_id 
            WHERE w.wrko_id = 3 AND ec.tpacrgo_backup = 'NO' 
            ORDER BY ec.tpawrko_id ASC LIMIT 1

SQL Server:

SELECT TOP 1 c.crgo_id,c.crgo_nombre 
            FROM workflow w 
            JOIN etapaworkflow ew ON w.wrko_id = ew.wrko_id 
            JOIN etapacargo ec ON ew.tpawrko_id = ec.tpawrko_id 
            JOIN cargo c ON c.crgo_id = ec.crgo_id 
            WHERE w.wrko_id = 1 AND ec.tpacrgo_backup = 'NO' 
            ORDER BY ec.tpawrko_id ASC

sql sql-server postgresql

asked by Manuel Mosquera 19.09.2016 в 16:16

source

2 answers

Conversion wchar a string to pass it to unicode Qt returns more than one SQL result under query for trigger?

score 1 · Answer 1

This version may work in both:

SELECT c.crgo_id,c.crgo_nombre
    FROM workflow w 
    JOIN etapaworkflow ew ON w.wrko_id = ew.wrko_id 
    JOIN etapacargo ec ON ew.tpawrko_id = ec.tpawrko_id 
    JOIN cargo c ON c.crgo_id = ec.crgo_id 
WHERE w.wrko_id = 3 AND ec.tpacrgo_backup = 'NO' 
ORDER BY ec.tpawrko_id ASC
OFFSET  0 ROWS 
FETCH FIRST 1 ROW ONLY

This is the form established by the SQL: 2008 standard for this functionality. According to the answer pointed out by @mauricio, SQL Server implements it from the 2012 version.

score 0 · Answer 2

@alvherre's answer is correct. But as you commented that it did not work for you in SQL Server, I must assume that you have an earlier version than SQL Server 2012.

If this is the case, I think that the only way to achieve an identical query for both databases is using row_number() :

select crgo_id,
       crgo_nombre
  from (select c.crgo_id,
               c.crgo_nombre,
               row_number() over (order by ec.tpawrko_id) as rn
          from workflow w 
          join etapaworkflow ew
            on w.wrko_id = ew.wrko_id 
          join etapacargo ec
            on ew.tpawrko_id = ec.tpawrko_id 
          join cargo c
            on c.crgo_id = ec.crgo_id 
         where w.wrko_id = 1 
           and ec.tpacrgo_backup = 'NO') t
 where rn = 1