Comment récupérer le premier et le dernier enregistrement d'une requête sql ?

Question

Dans PostgreSQL, j'exécute une requête avec plusieurs conditions qui renvoie plusieurs lignes, ordonnées par l'une des colonnes. Exemple :

SELECT <some columns> 
FROM mytable
<maybe some joins here>
WHERE <various conditions>
ORDER BY date DESC

Comment obtenir la première et la dernière ligne de cette requête ?

Answer 1

Pourquoi ne pas utiliser order by asc limit 1 et l'inverse, order by desc limit 1 ?

Answer 2

SELECT 
    MIN(Column), MAX(Column), UserId 
FROM 
    Table_Name
WHERE 
    (Conditions)
GROUP BY 
    UserId DESC

o

SELECT        
    MAX(Column) 
FROM            
    TableName
WHERE        
    (Filter)

UNION ALL

SELECT        
    MIN(Column)
FROM            
    TableName AS Tablename1
WHERE        
    (Filter)
ORDER BY 
    Column

Answer 3

Comment obtenir le premier et le dernier enregistrement de la base de données en c#.

SELECT TOP 1 * 
  FROM ViewAttendenceReport 
 WHERE EmployeeId = 4 
   AND AttendenceDate >='1/18/2020 00:00:00' 
   AND AttendenceDate <='1/18/2020 23:59:59'
 ORDER BY Intime ASC
 UNION
SELECT TOP 1 * 
  FROM ViewAttendenceReport 
 WHERE EmployeeId = 4 
   AND AttendenceDate >='1/18/2020 00:00:00' 
   AND AttendenceDate <='1/18/2020 23:59:59' 
 ORDER BY OutTime DESC;

Answer 4

Je pense que ce code est le même et qu'il est plus facile à lire.

SELECT <some columns> 
FROM mytable
<maybe some joins here>
WHERE date >= (SELECT date from mytable)
OR date <= (SELECT date from mytable);

Answer 5

Le code correct est indiqué ci-dessous

SELECT * FROM (SELECT city, length(city) FROM station WHERE LENGTH(city)=(SELECT MIN(LENGTH(city)) FROM station) ORDER BY city ) LIMIT 1;

SELECT * FROM (SELECT city, length(city) FROM station WHERE LENGTH(city)=(SELECT MAX(LENGTH(city)) FROM station) ORDER BY city ) LIMIT 1;