Select only the first line of the table [duplicate]

0

I need to select only row 1 from the table, I already used DISTINCT and it did not work

Follow 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)

;

Continuetopic: Select the line with the oldest date within that query between repeated values

    
asked by anonymous 23.03.2017 / 17:19

4 answers

2

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);
    
23.03.2017 / 17:21
0

See if it's true ...

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;
    
23.03.2017 / 19:18
0

I think there is a conceptual error in your query.

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

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

Good luck!

    
24.03.2017 / 15:57
0
  

(...) I need to get only the oldest data among the repeats.

Jander, considering the code that 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 value of the pair (DATA_REMESSA, PATRIMONIO) one should only return the line with the lowest value for DATA_Open, 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 required; evaluate locally.

    
23.03.2017 / 18:27