Table Group By - Tsql -

Answer 1

If you want to partition by OrarioA only:

SELECT OrarioA, OrarioB, IDOrario, IDDettaglioOrarioA, IDDettaglioOrarioB
FROM (
  SELECT *, ROW_NUMBER() OVER (PARTITION BY OrarioA ORDER BY OrarioB DESC, IDOrario DESC) AS rn
  FROM table
  ) t
WHERE rn = 1

, this will result in the resultset as on picture 2.

If you want to partition by OrarioA and OrarioB:

SELECT OrarioA, OrarioB, IDOrario, IDDettaglioOrarioA, IDDettaglioOrarioB
FROM (
  SELECT *, ROW_NUMBER() OVER (PARTITION BY OrarioA, OrarioB ORDER BY IDOrario DESC) AS rn
  FROM table
  ) t
WHERE rn = 1

, but this won't filter out the first row in your resultset.

Answer 2

How can I solve my problem using your code?

Thanks for you attention!

This is my real query:

WITH Tb_Linee AS
(
 SELECT * FROM VW_rellineestazionamenti 
 WHERE 
 IDLINEA = @IDLINEA 
 AND 
 IDStazA = @IDStazA 
 AND
 IDStazB = @IDStazB 
 AND
 PosizioneA = @PosizioneA
 AND
 PosizioneB = @PosizioneB
)


select OrarioA, OrarioB, VW_DettaglioOrariLinee_FromAToB.IDOrario, dbo.VW_DettaglioOrariLinee_FromAToB.IDDettaglioOrarioA, dbo.VW_DettaglioOrariLinee_FromAToB.IDDettaglioOrarioB
FROM         dbo.VW_DettaglioOrariLinee_FromAToB INNER JOIN
       Tb_Linee ON dbo.VW_DettaglioOrariLinee_FromAToB.IDRelA = Tb_Linee.IDRelA AND 
       dbo.VW_DettaglioOrariLinee_FromAToB.IDRelB = Tb_Linee.IDRelB INNER JOIN
       dbo.periodi ON dbo.VW_DettaglioOrariLinee_FromAToB.IDOrario = dbo.periodi.IDOrario INNER JOIN
       dbo.relgiornisettimanaorarilinee ON dbo.VW_DettaglioOrariLinee_FromAToB.IDOrario = dbo.relgiornisettimanaorarilinee.IDOrario

Answer 3

From the data you explained in your screenshots, I understand it as you need the id fields for the most highest IDOrario grouped by OrarioA and OrarioB?

SELECT IDDettaglioOrarioA, IDDettaglioOrarioB
FROM TABLE 
GROUP BY
OrarioA, OrarioB
ORDER BY IDOrario DESC

If you need to select more columns, select any more at will.

Answer 4

Hi, Yes I need what I've written but my real query is:

How Can I use your query?

select OrarioA, OrarioB, MAX(VW_DettaglioOrariLinee_FromAToB.IDOrario), dbo.VW_DettaglioOrariLinee_FromAToB.IDDettaglioOrarioA, dbo.VW_DettaglioOrariLinee_FromAToB.IDDettaglioOrarioB
FROM         dbo.VW_DettaglioOrariLinee_FromAToB INNER JOIN
       Tb_Linee ON dbo.VW_DettaglioOrariLinee_FromAToB.IDRelA = Tb_Linee.IDRelA AND 
       dbo.VW_DettaglioOrariLinee_FromAToB.IDRelB = Tb_Linee.IDRelB INNER JOIN
       dbo.periodi ON dbo.VW_DettaglioOrariLinee_FromAToB.IDOrario = dbo.periodi.IDOrario INNER JOIN
       dbo.relgiornisettimanaorarilinee ON dbo.VW_DettaglioOrariLinee_FromAToB.IDOrario = dbo.relgiornisettimanaorarilinee.IDOrario