Select in ordine per numero di record

[Tommy03]

Salve, ho una tabella "sfide" con questi campi:
-sfidante (INT, contiene l'id dell'utente che ha lanciato la sfida)
-sfidato (INT, contiene l'id dell'utente che ha accettato la sfida)
-vincente (INT, contiene l'id dell'utente che ha vinto la sfida)

Ciò che devo fare pensavo fosse banale ma per me non lo è. In pratica vorrei per un determinato utente che visita il sito (con ID = x) che vedesse l'avversario con cui ha avuto il maggior numero di vittorie, il problema è che x può essere sia "sfidante" che "sfidato". Qualcuno sa aiutarmi?
P.S. se serve c'è anche un'altra tabella ("users") che contiene gli id di tutti gli utenti.
Grazie

 

[IClaude]

credo che tu debba fare select count di select count ... e così via ... select nidificate ...

 

[Tommy03]

Grazie, ci ho provato, ma il problema è che l'avversario può essere sia nel campo "sfidante" che "sfidato" e quindi non so quale prendere

 

[marino51]

non so se ho capito bene, provo a darti un suggerimento come costruire la query,

"utente che visita il sito (con ID = x) che vedesse l'avversario con cui ha avuto il maggior numero di vittorie"

tutti gli avversari li chiamiamo y per comodità,

ora selezioniamo i record con sfidante = x (alias X) e sfidato (alias Y) e inseriamoci un CASE WHEN (ricordi ?)
ovvero se sfidante = vincente restituisce 1 altrimenti 0 (alias RISULTATO)

UNION

ora selezioniamo i record con sfidato = x (alias X) e sfidante (alias Y) ed inseriamoci il CASE WHEN
ovvero se sfidato = vincente restituisce 1 altrimenti 0 (alias RISULTATO)

a questo punto abbiamo un set con X = sfidante/sfidato, Y = sfidato/sfidante e risultato = 1 / 0 se X ha vinto o perso

select su questo set di X, Y e somma(risultato) raggruppato per X e Y e ordinato per la somma decrescente

dovrebbe essere tutto,

 

[Tommy03]

ok grazie mille per il suggerimento, ora ci provo

 

[Tommy03]

Ciao Marino,
prima di tutto ti illustro i record che ci sono dentro alla tabella:

Quindi per l'utente con id=78593169600496 ci sono 6 vittorie (con 4 avversari diversi) e 2 pareggi (indicati con 111)

Ho provato con questa query:

SELECT sfidante as x, sfidato as y, CASE WHEN sfidante = vincente THEN 1 ELSE 0 END AS vittoria FROM sfide WHERE sfidante = 78593169600496
UNION 
SELECT sfidato as x,sfidante as y, CASE WHEN sfidato = vincente THEN 1 ELSE 0 END AS vittoria FROM sfide WHERE sfidato = 78593169600496

E mi restituisce così:

Quindi per i pareggi mi da correttamente 0, per le vittorie mette 1 per ciascuno utente, anche ai due avversari con cui ha vinto 2 volte.

ora selezioniamo i record con sfidante = x (alias X) e sfidato (alias Y) e inseriamoci un CASE WHEN (ricordi ?)
ovvero se sfidante = vincente restituisce 1 altrimenti 0 (alias RISULTATO)

UNION

ora selezioniamo i record con sfidato = x (alias X) e sfidante (alias Y) ed inseriamoci il CASE WHEN
ovvero se sfidato = vincente restituisce 1 altrimenti 0 (alias RISULTATO)

Fino a qui quindi ho capito, ma non mi viene la parte finale.
Come posso procedere?
Grazie ancora

 

[Tommy03]

Aggiornamento, nel frattempo penso di essere riuscito a fare la parte finale che mi hai suggerito, raggruppando per avversario e sommando le vittorie, così:

SELECT y,SUM(vittoria) FROM
(SELECT sfidante as x, sfidato as y, CASE WHEN sfidante = vincente THEN 1 ELSE 0 END AS vittoria FROM sfide WHERE sfidante = 78593169600496
UNION
SELECT sfidato as x,sfidante as y, CASE WHEN sfidato = vincente THEN 1 ELSE 0 END AS vittoria FROM sfide WHERE sfidato = 78593169600496)
AS z GROUP BY y

E lo fa correttamente, però rimane sempre il problema che per la query del post precedente i risultati "doppi" non me li considera, e quindi vedo solo una vittoria anche contro gli avversari con cui ha vinto 2 volte.

Inoltre, tornando alla questione precedente (che mi riporta solo una riga per ogni avversario con cui ha vinto 2 volte) ho visto che se uso questa query (senza UNION, soltanto dove sfidante = x) me li prende tutti correttamente:

SELECT sfidante as x, sfidato as y, CASE WHEN sfidante = vincente THEN 1 ELSE 0 END AS vittoria FROM sfide WHERE sfidante = 78593169600496

Veramente non capisco perché quando faccio la UNION mi elimina i duplicati

 

[Tommy03]

RISOLTO!!! Grazie mille marino51, ho tentato (a caso) a mettere UNION ALL al posto di UNION e funziona.
Pubblico la query totale se dovesse servire a qualcuno:

SELECT y,SUM(vittoria) AS num_vittorie FROM
(SELECT sfidante as x, sfidato as y, CASE WHEN sfidante = vincente THEN 1 ELSE 0 END AS vittoria FROM sfide WHERE sfidante = 78593169600496
UNION ALL
SELECT sfidato as x,sfidante as y, CASE WHEN sfidato = vincente THEN 1 ELSE 0 END AS vittoria FROM sfide WHERE sfidato = 78593169600496)
AS z GROUP BY y ORDER BY num_vittorie DESC

Senza il tuo aiuto non ce l'avrei mai fatta, grazie ancora!