come fare una classifica con un'interrogazione SQL

[Micheal_Scofield]

Ciao a tutti, sono poco esperto e chiedo a voi.
ho una tabella di valori di questo tipo:
x
x
x
y
y
z
z
z
z
w
w
w

desidererei con un query ottenere questo risultato :
1 | z | 4
2 | x | 3
2 | w | 3
3 | y | 2
a pari COUNT deve essere ordinato in ordine alfabetico.

è possibile farlo? come?

[nuzzopippo]

Prova a guardare qua, non ho avuto modo di testarlo (lo farò domani) ma credo che una delle istruzioni riportate :

Codice: Seleziona tutto

SELECT nameprd AS "Name Product" , sum(conta) AS "files downloaded", count (itemidcontacts) AS "N.Users" 
       FROM (SELECT  nameprd , count (itemid) AS conta, itemidcontacts FROM contactsdownloads 
               WHERE createddate > '01-01-2007'
               GROUP BY nameprd, itemidcontacts
               ORDER BY conta DESC ) AS DL

     GROUP BY  nameprd
     ORDER BY sum(conta) DESC;

Sia del tipo che a Te serve

[Micheal_Scofield]

non capisco come venga fuori la posizione in classifica dalla tua query.
la posizione in classifica nel mio primo esempio è la prima colonna, poi c'è l'elemento e successivamente il numero totale di punti

[nuzzopippo]

Ed hai perfettamente ragione a non capirla

Mi stavo studiando una pagina che trovavo interessante rispetto al tuo quesito e sono stato interrotto per chiusura, ho segnalato la pagina, oltre tutto combinando un pasticcio perché la pagina che volevo segnalare era questa, con in particolare la query (presa da questa segnalazione) che trovavo promettente come esempio ma, leggendola meglio ora mi accorgo che non sarebbe efficace perché presupporrebbe un raggruppamento sulla funzione "count()", il che non è possibile.

Era molto interessante principalmente per la funzione "row_number()" che in postgresql permetterebbe di numerare le righe di una query, non la conoscevo ed ho reputato la faccenda abbastanza interessante da meritare una segnalazione. Comunque poco promettente per quel raggruppamento sul count() detto prima.

Strada possibile potrebbe essere tramite una "vista" (VIEW), appena mi sarà possibile cercherò di vedere se ci cavo qualcosa per Te interessante.

Chiedo scusa se per la fretta Ti ho fatto perdere tempo.

[thece]

su SQL Server (T-SQL) io farei così:

Codice: Seleziona tutto

SELECT
	ROW_NUMBER() OVER (ORDER BY T2.Count DESC, T2.SomeColumn) AS Position,
	T2.SomeColumn,
	T2.Count
FROM
(
	SELECT
		T1.SomeColumn,
		COUNT(T1.SomeColumn) AS Count
	FROM SomeTable AS T1
	GROUP BY T1.SomeColumn
) AS T2

[nuzzopippo]

... beh, ho fatto qualcosa di simile ma mooolto più arzicocolato, mi son picchiato con postgresql su macchina virtuale

Definita una tabella

Codice: Seleziona tutto

            Tabella "public.valori"
 Colonna |         Tipo         | Modificatori 
---------+----------------------+--------------
 dato    | character varying(5) | not null


 dato 
------
 x
 x
 x
 x
 y
 y
 z
 z
 z
 w
 w
 w
(12 rows)

 SELECT dato, COUNT(dato) FROM valori
GROUP BY dato
ORDER BY COUNT(dato) DESC;
 dato | count 
------+-------
 x    |     4
 w    |     3
 z    |     3
 y    |     2
(4 rows)

Ho creato una vista

Codice: Seleziona tutto

CREATE VIEW num_conti(dato, conta)
AS SELECT dato, COUNT(dato) FROM valori
GROUP BY dato
ORDER BY COUNT(dato) DESC;

select * from num_conti;
 dato | conta 
------+-------
 x    |     4
 w    |     3
 z    |     3
 y    |     2

e quindi interrogato la vista

Codice: Seleziona tutto

SELECT (SELECT COUNT(*) + 1 FROM (SELECT conta FROM num_conti GROUP BY conta ORDER BY conta DESC) b WHERE b.conta > num_conti.conta) AS classifica, dato, conta FROM num_conti ORDER BY conta DESC;
 classifica | dato | conta 
------------+------+-------
          1 | x    |     4
          2 | w    |     3
          2 | z    |     3
          3 | y    |     2

È sempre istruttivo guardare chi ne sa di più o come funziona su sistemi diversi ... proverò a vedere la query di @thece

[Micheal_Scofield]

su SQL Server (T-SQL) io farei così:

Codice: Seleziona tutto

SELECT
	ROW_NUMBER() OVER (ORDER BY T2.Count DESC, T2.SomeColumn) AS Position,
	T2.SomeColumn,
	T2.Count
FROM
(
	SELECT
		T1.SomeColumn,
		COUNT(T1.SomeColumn) AS Count
	FROM SomeTable AS T1
	GROUP BY T1.SomeColumn
) AS T2

questa soluzione associa una posizione diversa per un uguale punteggio.
invece io voglio che le voci a pari punti abbiano la stessa posizione, nel mio esempio vedi che i due record piazzati al secondo posto.

[Micheal_Scofield]

... beh, ho fatto qualcosa di simile ma mooolto più arzicocolato, mi son picchiato con postgresql su macchina virtuale

Definita una tabella

Codice: Seleziona tutto

            Tabella "public.valori"
 Colonna |         Tipo         | Modificatori 
---------+----------------------+--------------
 dato    | character varying(5) | not null


 dato 
------
 x
 x
 x
 x
 y
 y
 z
 z
 z
 w
 w
 w
(12 rows)

 SELECT dato, COUNT(dato) FROM valori
GROUP BY dato
ORDER BY COUNT(dato) DESC;
 dato | count 
------+-------
 x    |     4
 w    |     3
 z    |     3
 y    |     2
(4 rows)

Ho creato una vista

Codice: Seleziona tutto

CREATE VIEW num_conti(dato, conta)
AS SELECT dato, COUNT(dato) FROM valori
GROUP BY dato
ORDER BY COUNT(dato) DESC;

select * from num_conti;
 dato | conta 
------+-------
 x    |     4
 w    |     3
 z    |     3
 y    |     2

e quindi interrogato la vista

Codice: Seleziona tutto

SELECT (SELECT COUNT(*) + 1 FROM (SELECT conta FROM num_conti GROUP BY conta ORDER BY conta DESC) b WHERE b.conta > num_conti.conta) AS classifica, dato, conta FROM num_conti ORDER BY conta DESC;
 classifica | dato | conta 
------------+------+-------
          1 | x    |     4
          2 | w    |     3
          2 | z    |     3
          3 | y    |     2

È sempre istruttivo guardare chi ne sa di più o come funziona su sistemi diversi ... proverò a vedere la query di @thece

ci sono soluzioni senza usare Viste?

[thece]

questa soluzione associa una posizione diversa per un uguale punteggio.
invece io voglio che le voci a pari punti abbiano la stessa posizione, nel mio esempio vedi che i due record piazzati al secondo posto.

Ops ... è vero, dalla tua richiesta mi è sfuggito questo requisito

[Micheal_Scofield]

direi che DENSE_RANK() fa quello che mi serve
grazie lo stesso dell'aiuto

[thece]

Codice: Seleziona tutto

SELECT
	DENSE_RANK() OVER (ORDER BY T2.Count DESC) AS Position,
	T2.SomeColumn,
	T2.Count
FROM
(
	SELECT
		T1.SomeColumn,
		COUNT(T1.SomeColumn) AS Count
	FROM SomeTable AS T1
	GROUP BY T1.SomeColumn
) AS T2
ORDER BY Position, T2.SomeColumn