J'ai une requête: chaque condition Y a-t-il un moyen d'y parvenir ou de la solution de contournement? p> p> nom = ... et éventuellement <... code> peut retourner plusieurs lignes, mais j'ai besoin que de < Strong> Premier Strort> Ligne de chaque
ou Code> Condition selon
Commander par code> Clause. P>
5 Réponses :
Il me semble que vous recherchez Row_Number ()
select * from ( SELECT ID, Name, EventTime , row_number() over(partition by Name order by EventTime DESC) rn FROM table WHERE Name = 'value1' AND EventTime < timeValue1 OR Name = 'value2' AND EventTime < timeValue2 OR Name = 'value3' AND EventTime < timeValue3 ... ) a where a.rn=1
Vous pouvez essayer:
SELECT Top (1) ID, Name, EventTime FROM table WHERE Name = 'value1' AND EventTime < timeValue1 OR Name = 'value2' AND EventTime < timeValue2 OR Name = 'value3' AND EventTime < timeValue3 ... ORDER BY EventTime DESC
Tant que tous vos noms de colonne sont les mêmes que vous pouvez utiliser des syndicats.
SELECT top 1 ID, Name, EventTime FROM table WHERE Name = 'value1' AND EventTime < timeValue1 ORDER BY EventTime DESC UNION SELECT top 1 ID, Name, EventTime FROM table WHERE Name = 'value2' AND EventTime < timeValue2 ORDER BY EventTime DESC UNION SELECT top 1 ID, Name, EventTime FROM table WHERE Name = 'value3' AND EventTime < timeValue3 ORDER BY EventTime DESC
; WITH A AS ( SELECT *, ROW_NUMBER()OVER(PARTITION BY [GROUP] ORDER BY 1) as RN FROM ( SELECT ID, Name, EventTime, CASE WHEN Name = 'value1' AND EventTime < timeValue1 THEN '1' WHEN Name = 'value2' AND EventTime < timeValue2 THEN '2' WHEN Name = 'value3' AND EventTime < timeValue3 THEN '3' WHEN Name = 'value3' AND EventTime < timeValue4 THEN '4' --Could be more here END as [Group] FROM table ) ) SELECT * FROM A WHERE RN = 1 ORDER BY EventTime DESC
Je pense que je m'approcherais comme suit:
SELECT ID, Name, EventTime FROM (SELECT t.*, ROW_NUMBER() OVER (PARTITION BY name ORDER BY t.eventtime DESC) as seqnum FROM table t JOIN (VALUES ('value1', timevalue1), ('value2', timevalue2), ('value3', timevalue3) ) v(name, timevalue) ON t.name = v.name and t.eventtime < v.timevalue ) t WHERE seqnum = 1 ORDER BY EventTime DESC;
première ligne basée sur quel ordre? b>
@Zoharpeled basé sur
EventTtime Desc code> qui signifie la ligne avec max
EventTime code>