Ordered selection of 2 columns in single SQL query
-
Hey, I have a table named volleyballsecondround8 which has columns like 'Winner', 'Loser' and 'MatchNo'. I'd like to make a SELECT statement as it takes the 'Winner' where 'MatchNo'='M20' and puts it ind the [0]. row of my new table, then takes the 'Loser' of 'MatchNo'='M20' and puts it into the second row. Therefore; When I execute a query on volleyballsecondround8 like the following:
Select @row := @row + 1 as row,Winner,Loser
from volleyballsecondround8,(SELECT @row := 0) r
Where MatchNo IN('M20','M19','M18','M17') OrderI get e resultant table like: ROW-Winner-Loser ---------------- 1-India-Luxemburg 2-USA-Spain 3-Turkey-Argentina Where, I want a table like: ROW-TEAM ----------- 1-India 2-Luxemburg 3-USA 4-Spain 5-Turkey 6-Argentina any help?
-
Hey, I have a table named volleyballsecondround8 which has columns like 'Winner', 'Loser' and 'MatchNo'. I'd like to make a SELECT statement as it takes the 'Winner' where 'MatchNo'='M20' and puts it ind the [0]. row of my new table, then takes the 'Loser' of 'MatchNo'='M20' and puts it into the second row. Therefore; When I execute a query on volleyballsecondround8 like the following:
Select @row := @row + 1 as row,Winner,Loser
from volleyballsecondround8,(SELECT @row := 0) r
Where MatchNo IN('M20','M19','M18','M17') OrderI get e resultant table like: ROW-Winner-Loser ---------------- 1-India-Luxemburg 2-USA-Spain 3-Turkey-Argentina Where, I want a table like: ROW-TEAM ----------- 1-India 2-Luxemburg 3-USA 4-Spain 5-Turkey 6-Argentina any help?
SELECT Row,
0 AS resultorder,
Winner
FROM volleyballsecondround8
UNION ALL
SELECT Row,
1,
Loser
FROM volleyballsecondround8
ORDER BY Row ASC, resultorder ASC -
SELECT Row,
0 AS resultorder,
Winner
FROM volleyballsecondround8
UNION ALL
SELECT Row,
1,
Loser
FROM volleyballsecondround8
ORDER BY Row ASC, resultorder ASCthanks for help but I decided to take all the data to a DataTable and then sort it in the C# part of the project, the hard way =) thanks again