Select only 1st row of table

Question

I need to select only row 1 of the table, I’ve used the DISTINCT and it didn’t work

follows the Query:

select

 Max(remessa.dt_uso_inicio) as DATA_REMESSA
,min(dt_entorc_oficina)DATA_ABERTURA
,patr.nr_patrimonio AS PATRIMONIO

from 

orcos as o 
join 
patrimon as patr
on o.cd_PATRIMONIO= patr.cd_PATRIMONIO
join
v_remessa_patrimonio as remessa
on remessa.cd_patrimonio = patr.cd_patrimonio 

where patr.nr_patrimonio = '070-13 GEA'
group by
patr.nr_patrimonio
,o.dt_entorc_oficina
having o.dt_entorc_oficina > max(remessa.dt_uso_inicio)

;

---

Continuation topic: Select the line with the oldest date within this query between the repeated values

Answer 1

Use TOP(1).

 SELECT TOP(1) Max(remessa.dt_uso_inicio) AS DATA_REMESSA,
       min(dt_entorc_oficina)DATA_ABERTURA,
       patr.nr_patrimonio AS PATRIMONIO
FROM orcos AS o
JOIN patrimon AS patr ON o.cd_PATRIMONIO= patr.cd_PATRIMONIO
JOIN v_remessa_patrimonio AS remessa ON remessa.cd_patrimonio = patr.cd_patrimonio
WHERE patr.nr_patrimonio = '070-13 GEA'
GROUP BY patr.nr_patrimonio,
         o.dt_entorc_oficina
HAVING o.dt_entorc_oficina > max(remessa.dt_uso_inicio);

Answer 2

I think there is a conceptual error in your query.

The dt_entorc_oficina field is in gtoup by and min() at the same time, which is very strange.

Assuming you put min() to try to bring a single query, I suggest you take this min() and do as the colleagues above said, which is to use select top 1.

Good luck!

Answer 3

(...) I need to take only the oldest data among the repeated.

Jander, considering the code you posted at the beginning of this topic, it seems to me that only the GROUP BY clause needs to be rewritten. If for each pair value (DATA_REMESSA, PATRIMONIO) one must return only the line with the lowest value for DATA_ABERTURA, then the code would look like this:

-- código #1
SELECT DATA_REMESSA= remessa.dt_uso_inicio,
       DATA_ABERTURA= min(o.dt_entorc_oficina),
       PATRIMONIO= patr.nr_patrimonio
  from orcos as o 
       inner join patrimon as patr on o.cd_PATRIMONIO = patr.cd_PATRIMONIO
       inner join v_remessa_patrimonio as remessa on remessa.cd_patrimonio = patr.cd_patrimonio 
  --where patr.nr_patrimonio = '070-13 GEA'
  group by patr.nr_patrimonio, remessa.dt_uso_inicio;

The initial impression I have is that the HAVING clause is not necessary; evaluate locally.

Answer 4

see if this is right...

SELECT TOP 1

 MAX(remessa.dt_uso_inicio) DATA_REMESSA
,MIN(dt_entorc_oficina) DATA_ABERTURA
,patr.nr_patrimonio PATRIMONIO

FROM

orcos o 
JOIN 
 patrimon patr
 ON o.cd_PATRIMONIO= patr.cd_PATRIMONIO
JOIN
 v_remessa_patrimonio remessa
 ON remessa.cd_patrimonio = patr.cd_patrimonio 

WHERE patr.nr_patrimonio = '070-13 GEA'
GROUP BY
 patr.nr_patrimonio
 ,o.dt_entorc_oficina
 having o.dt_entorc_oficina > max(remessa.dt_uso_inicio)
ORDER BY DATA_REMESSA DESC;