Grouped ordered precise select statement?
-
Hello everyone I have a table named "volleyballpool8" it has columns as ID, Pool, TeamNo, Team and Point There are 11 rows in this table and there are 4 different "Pool"s, as A,B,C and D Now I need to make a SELECT statement to form a table, which: 1-Gets the ID, Team and Point of the row with the HIGHEST "Point" value of Pool='A's 2-adds the ID, Team and Point of the row with the SECOND HIGHEST "Point" value of Pool='B's 3-adds the ID, Team and Point of the row with the HIGHEST "Point" value of Pool='C's 4-adds the ID, Team and Point of the row with the SECOND HIGHEST "Point" value of Pool='D's Therefore, in the end, I'll have a 4 rowed table I'm somewhat new to SQL and I got confused... Can anybody help? :doh: btw: all of the fields are strings
-
Hello everyone I have a table named "volleyballpool8" it has columns as ID, Pool, TeamNo, Team and Point There are 11 rows in this table and there are 4 different "Pool"s, as A,B,C and D Now I need to make a SELECT statement to form a table, which: 1-Gets the ID, Team and Point of the row with the HIGHEST "Point" value of Pool='A's 2-adds the ID, Team and Point of the row with the SECOND HIGHEST "Point" value of Pool='B's 3-adds the ID, Team and Point of the row with the HIGHEST "Point" value of Pool='C's 4-adds the ID, Team and Point of the row with the SECOND HIGHEST "Point" value of Pool='D's Therefore, in the end, I'll have a 4 rowed table I'm somewhat new to SQL and I got confused... Can anybody help? :doh: btw: all of the fields are strings
I would appreciate any answer that makes the full query above but with some brute coding, I can handle myself in .cs part of the project if you only tell me how to get a "SECOND HIGHEST" from a table for the highest of A's, I'm using
SELECT ID as row, Team, Point FROM cadet_games.volleyballpool8 WHERE Pool = 'A' and Point=(select max(point) from cadet_games.volleyballpool8)If I don't get answer soon enough, I'll have to use multiple dataTables to concat later. But still I need some "second highest" query :( -
Hello everyone I have a table named "volleyballpool8" it has columns as ID, Pool, TeamNo, Team and Point There are 11 rows in this table and there are 4 different "Pool"s, as A,B,C and D Now I need to make a SELECT statement to form a table, which: 1-Gets the ID, Team and Point of the row with the HIGHEST "Point" value of Pool='A's 2-adds the ID, Team and Point of the row with the SECOND HIGHEST "Point" value of Pool='B's 3-adds the ID, Team and Point of the row with the HIGHEST "Point" value of Pool='C's 4-adds the ID, Team and Point of the row with the SECOND HIGHEST "Point" value of Pool='D's Therefore, in the end, I'll have a 4 rowed table I'm somewhat new to SQL and I got confused... Can anybody help? :doh: btw: all of the fields are strings
Hi, question: what is the second highest in this collection: 4, 4, 2, 1? is it 4 or 2? Anyway, you should look here[^]. :)
Luc Pattyn [Forum Guidelines] [Why QA sucks] [My Articles] Nil Volentibus Arduum
Please use <PRE> tags for code snippets, they preserve indentation, and improve readability.
-
Hi, question: what is the second highest in this collection: 4, 4, 2, 1? is it 4 or 2? Anyway, you should look here[^]. :)
Luc Pattyn [Forum Guidelines] [Why QA sucks] [My Articles] Nil Volentibus Arduum
Please use <PRE> tags for code snippets, they preserve indentation, and improve readability.
well, there are lots of fields in this table and some of them are "for, against"...etc I'm adding those to my query as well, now, just think that Point column is unique :D btw, thanks for the answer, I'll have a look at it
-
Hi, question: what is the second highest in this collection: 4, 4, 2, 1? is it 4 or 2? Anyway, you should look here[^]. :)
Luc Pattyn [Forum Guidelines] [Why QA sucks] [My Articles] Nil Volentibus Arduum
Please use <PRE> tags for code snippets, they preserve indentation, and improve readability.
Hey! :D be sure that I've done that before I ask to you guys =) I'm googling and querying at the same time :)
-
Hello everyone I have a table named "volleyballpool8" it has columns as ID, Pool, TeamNo, Team and Point There are 11 rows in this table and there are 4 different "Pool"s, as A,B,C and D Now I need to make a SELECT statement to form a table, which: 1-Gets the ID, Team and Point of the row with the HIGHEST "Point" value of Pool='A's 2-adds the ID, Team and Point of the row with the SECOND HIGHEST "Point" value of Pool='B's 3-adds the ID, Team and Point of the row with the HIGHEST "Point" value of Pool='C's 4-adds the ID, Team and Point of the row with the SECOND HIGHEST "Point" value of Pool='D's Therefore, in the end, I'll have a 4 rowed table I'm somewhat new to SQL and I got confused... Can anybody help? :doh: btw: all of the fields are strings
Couldn't find a single query to do all above, but decided to do it C# style thanks all anyway :)
-
Couldn't find a single query to do all above, but decided to do it C# style thanks all anyway :)
why is that? the first answer in the first Google hit shows a principle that should work well (assuming you want the second highest distinct value): have a subquery determining the maximum value, then query for the largest one that is less than the maximum. :)
Luc Pattyn [Forum Guidelines] [Why QA sucks] [My Articles] Nil Volentibus Arduum
Please use <PRE> tags for code snippets, they preserve indentation, and improve readability.
-
Hello everyone I have a table named "volleyballpool8" it has columns as ID, Pool, TeamNo, Team and Point There are 11 rows in this table and there are 4 different "Pool"s, as A,B,C and D Now I need to make a SELECT statement to form a table, which: 1-Gets the ID, Team and Point of the row with the HIGHEST "Point" value of Pool='A's 2-adds the ID, Team and Point of the row with the SECOND HIGHEST "Point" value of Pool='B's 3-adds the ID, Team and Point of the row with the HIGHEST "Point" value of Pool='C's 4-adds the ID, Team and Point of the row with the SECOND HIGHEST "Point" value of Pool='D's Therefore, in the end, I'll have a 4 rowed table I'm somewhat new to SQL and I got confused... Can anybody help? :doh: btw: all of the fields are strings
Impatient bugger aren't you. Look into
row_numberandpartition overin BOL. With these 2 commands you can select the results grouped by team and ordered by points with a ranking per team. With that sub query you simply select the top 4 record to give you the results. I can think of 2 other methods of achieving this in TSQL bit the above key word should give you the pointers for research.Never underestimate the power of human stupidity RAH
-
Hello everyone I have a table named "volleyballpool8" it has columns as ID, Pool, TeamNo, Team and Point There are 11 rows in this table and there are 4 different "Pool"s, as A,B,C and D Now I need to make a SELECT statement to form a table, which: 1-Gets the ID, Team and Point of the row with the HIGHEST "Point" value of Pool='A's 2-adds the ID, Team and Point of the row with the SECOND HIGHEST "Point" value of Pool='B's 3-adds the ID, Team and Point of the row with the HIGHEST "Point" value of Pool='C's 4-adds the ID, Team and Point of the row with the SECOND HIGHEST "Point" value of Pool='D's Therefore, in the end, I'll have a 4 rowed table I'm somewhat new to SQL and I got confused... Can anybody help? :doh: btw: all of the fields are strings
As Luc Pattyn said get top 1 value which is lower then max value, Look this example and hope you will figure out with your case:
SELECT MAX(vlera) HighestValue, (SELECT TOP 1 m.vlera FROM mytable m WHERE m.vlera < (SELECT MAX(vlera) FROM mytable) ORDER BY vlera DESC) secondHihgestValue FROM mytable
I Love T-SQL "Don't torture yourself,let the life to do it for you." If my post helps you kindly save my time by voting my post. www.aktualiteti.com