Query result as column name?!?

[Baukep]

Hello,

Back again after several months. Played fine with SQL and most (with a little help from forummembers) went well.
I now have made a view with results of several years for all the people in the database.

I can query the view to get the three winners, sorted decending on Winstperc. The result looks like this:

Seizoen Naam Winstperc
2006/07 Jim 1,0909
2006/07 Bill 1,0896
2006/07 William 1,0879

And that's exactly what I want to know but I need it like this:

Seizoen Nr1 Nr2 Nr3
2006/07 Jim Bill William

Looking through the threads I got some nice ideas but...
I tried it with IF and with Case but never succeeded.

So, I think I'm overlooking something. Please show me the way?

Thanks in advance and excuse my use of English (my Dutch is better)

Bauke

 

[r937]

please show your query

r937.com | rudy.ca

 

[Baukep]

Sorry, here it is. It's very simple because most of the work is done in producing the view.

SELECT Seizoen
, Naam
, Winstperc
FROM winnaars
WHERE Seizoen=(SELECT(MAX(w_seizoen)) FROM wedstrijden)
ORDER BY Winstperc DESC
LIMIT 3

Bauke

 

[r937]

this query is a great example of how not to produce a result with SQL that should be produced by the front end application

look how simple your query is, and how messy it gets if all you want is to print the 3 values on the same row...

SELECT Seizoen
     , MAX(Nr1) as Nr1
     , MAX(Nr2) as Nr2
     , MAX(Nr3) as Nr3
  FROM ( 
SELECT Seizoen
     , Naam as Nr1
     , CAST(NULL as CHAR) as Nr2
     , CAST(NULL as CHAR) as Nr3
  FROM winnaars
 WHERE Seizoen=(SELECT MAX(w_seizoen) FROM wedstrijden)
ORDER 
    BY Winstperc DESC LIMIT 0,1
UNION ALL
SELECT Seizoen
     , NULL
     , Naam
     , NULL
  FROM winnaars
 WHERE Seizoen=(SELECT MAX(w_seizoen) FROM wedstrijden)
ORDER 
    BY Winstperc DESC LIMIT 1,1
UNION ALL
SELECT Seizoen
     , NULL
     , NULL
     , Naam
  FROM winnaars
 WHERE Seizoen=(SELECT MAX(w_seizoen) FROM wedstrijden)
ORDER 
    BY Winstperc DESC LIMIT 2,1
       ) as T
GROUP
    BY Seizoen

r937.com | rudy.ca

 

[Baukep]

Thanks Rudy,

Looks great but I get the error 1221 Incorrect usage of UNION and ORDER BY

Any idea what's wrong? (I copied and pasted it)

Bauke

 

[r937]

oh, yes, you're right

try it with an extra couple layers of parentheses...

SELECT Seizoen
     , MAX(Nr1) as Nr1
     , MAX(Nr2) as Nr2
     , MAX(Nr3) as Nr3
  FROM ( 
SELECT * FROM (
( SELECT Seizoen
     , Naam as Nr1
     , CAST(NULL as CHAR) as Nr2
     , CAST(NULL as CHAR) as Nr3
  FROM winnaars
 WHERE Seizoen=(SELECT MAX(w_seizoen) FROM wedstrijden)
ORDER 
    BY Winstperc DESC LIMIT 0,1 )
UNION ALL
( SELECT Seizoen
     , NULL
     , Naam
     , NULL
  FROM winnaars
 WHERE Seizoen=(SELECT MAX(w_seizoen) FROM wedstrijden)
ORDER 
    BY Winstperc DESC LIMIT 1,1 )
UNION ALL
( SELECT Seizoen
     , NULL
     , NULL
     , Naam
  FROM winnaars
 WHERE Seizoen=(SELECT MAX(w_seizoen) FROM wedstrijden)
ORDER 
    BY Winstperc DESC LIMIT 2,1 ) 
       ) as T2
       ) as T
GROUP
    BY Seizoen

r937.com | rudy.ca

 

[Baukep]

Thanks again!

But now I get 1271 Illegal mix of collations for operation 'Union'.
I looked it up but I don't understand it. I'm always using the same base and have never changed a collation. Working with XAMPP (latest version)and Navicat as a query and reporting tool.

Is it possible to set the collation in this query and if yes, where and how?

Thanks again for helping me out

Bauke