CODEWITHSUNDEEP
sql-serverdatabaset-sqlgroup-by
Giomuti
Giomuti

Reputation: 301

Table Group By - Tsql -

alt text http://img187.imageshack.us/img187/8453/testhc3.png

alt text http://img145.imageshack.us/img145/3306/test2bn4.png

The first picture is my query. I need to obtain results in second picture.

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

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

Upvotes: 1

Views: 808

Answers (2)

Quassnoi
Quassnoi

Reputation: 425743

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.

Upvotes: 3

achinda99
achinda99

Reputation: 5078

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.

Upvotes: 0

Related Questions