|
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?
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 
|
|
|
|
|
Hi,
question: what is the second highest in this collection: 4, 4, 2, 1? is it 4 or 2?
Anyway, you should look here[^].
|
|
|
|
|
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 
btw, thanks for the answer, I'll have a look at it
|
|
|
|
|
Hey!
be sure that I've done that before I ask to you guys =)
I'm googling and querying at the same time
|
|
|
|
|
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.
|
|
|
|
|
Impatient bugger aren't you.
Look into row_number and partition over in 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
|
|
|
|
|
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,<br />
(SELECT TOP 1 m.vlera FROM mytable m WHERE m.vlera < (SELECT MAX(vlera) FROM mytable) ORDER BY vlera DESC) secondHihgestValue<br />
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
|
|
|
|
|
I have a set of records which I want to insert to the table at one stretch. For this, I am using OpenXML keyword in Sql Server 2005.
Following is my stored procedure:
ALTER procedure [dbo].[usp_InsertManyRows]
@XMLDOC xml
AS
declare @xml_hndl int
exec sp_xml_preparedocument @xml_hndl OUTPUT, @XMLDOC
Insert Into outputfiles
(
Processid
)
Select
Processid
From
OPENXML(@xml_hndl, '/Process/ProcessId')
With
(
Processid int '/Process/ProcessId'
)
The table 'OuputFiles' contains column ProcessId, OutputFileName and ContributionFileName columns. When I tried to test the sp as below, it was inserting the same record multiple times. I am not sure why it is:
exec usp_InsertManyRows '<Process><ProcessId>8712</ProcessId><ProcessId>14444</ProcessId></Process>'
It is only inserting 8712 two times, not at all 14444.
I am using this for the first time. Please help me how to resolve this, as well as inserting the values for multiple columns like OutputFileName and ContributionFileName columns.
Thanks in advance
Success is the good fortune that comes from aspiration, desperation, perspiration and inspiration.
|
|
|
|
|
|
Hi!
How to get the total records in a table? The following query returns an irrelevant number like 39685832(not the same all the time). I'm using SQLite. How to get the no.of records from a SQLite table?
"SELECT COUNT(*) FROM current"
modified on Tuesday, October 5, 2010 7:23 AM
|
|
|
|
|
Your table does have a primary key field, or unique index at least, doesn't it? Try counting on that, eg
SELECT COUNT(ID) FROM tablename
Is "current" really your table name? I am not sure about suing that... it is a reserved word[^] - at least try enclosing it in delimters if you must use that name. Otherwise, change it.
|
|
|
|
|
Hi!
This query doesn't work. Actually I'm passing SQLite query from C++. When I run my C++ program, it crashes and the console displays "SQLError: No such column ID". I've tested this with another table(profile instead of current)also. How to get the number of rows in a table?
|
|
|
|
|
Well use the name of a column you do have then, if ytou haven't got an ID field.
|
|
|
|
|
Can you tell me the type(INTEGER or TEXT) that the above query returns?
|
|
|
|
|
It should return an integer
|
|
|
|
|
It's printed correctly. Query's return type is an array string(I've implemented like that). So, I've to convert it to int using atoi.
|
|
|
|
|
hey guys..i try to insert data in two tables which are related with each other... the first table name is
Mark and the columns mark_id and mark_name
and the second table name is Model
the columns are model_id,Model_name and mark_id and mark_id is FK here
now here is the thing i want to do for example i want to add Honda as Mark to mark table and the model Jazz,Civic etc as models that depends on the HOnda..To do that i created a stored Procedure like below
Create Procedure sp_TablolaraEkle
@marka_ad nvarchar(50),
@model_ad nvarchar(50)
as
Begin Try
Begin Transaction
Set Nocount on
Declare @id int
Declare @Marka nvarchar(50)
insert into Marka(marka_ad) values (@marka_ad)
Select @id=@@Identity
Declare @model_adi nvarchar(50)
insert into Model(marka_id,model_ad)values (@id,@model_ad)
Commit
End Try
Begin Catch
if @@Trancount>0
Rollback
End Catch
but the problem is for example i added Ferrari to mark table with id is 3 i can add its model too but when i want to add one more Ferrari model then it adds Ferrari model with different mark_id in model table
can u help me how it will get rid off the problem...thanks for your help
modified on Tuesday, October 5, 2010 5:28 AM
|
|
|
|
|
The procedure will always return a success. Since you catch any exception that occurs and rollback the SQL statement to insert data.
Your procedure basically inserts a row in the Marka table with the value passed to the stored procedure. Then it attempts to create a new record in Model based upon the identity field of the Marka table (@@identity).
There are a few weird things though. You never assign any value to @model_ad when you are using it to create a new record in Model. This could cause an issue.
To find out what exactly is going wrong remove the 'begin try', 'end try', 'begin catch' and 'end catch'. This should display the error, but remember to put these things back afterwards.
|
|
|
|
|
hii friends..thanks for answer..i did what u said it has given error and i fxed it now...but still my basic problem goes on...is it becasue the id column is identity in Mark table ?
when i add a new row it increase by 1 although i have same data already...For example i save a new data MArk as Fiat and the model is Linea it gives number 16 as id in mark table and it goes to model atble and it writes there too...it is ok till now..but when i want to add one more data mark as Fiat this time give number 17 as id and in model table too.. i want it to be 16...how i will manage it ??
|
|
|
|
|
Before adding the Mark record (ferarri) you need to check if the is an existing record
Select @ID = MarkID from MarkTable where Model = 'ferarri'
If isnull(@id,0) = 0
begin
Insert both records
end
else
begin
insert FK record only
end
Never underestimate the power of human stupidity
RAH
|
|
|
|
|
Alter Procedure sp_TablolaraEkle
@marka_ad nvarchar(50),
@model_ad nvarchar(50)
as
Begin
Set Nocount on
Declare @id int
Declare @Marka nvarchar(50)
Select @id=id From Marka where marka_ad=@marka_ad
IF isnull(@id,0)=0
BEGIN
INSERT INTO Marka(marka_ad) values (@marka_ad)
SELECT @id=@@Identity
insert into Model(marka_id,model_ad)values (@id,@model_ad)
END
Else
BEGIN
insert into Model (marka_id,model_ad) values(@id,@model_ad)
END
End
ok man i altered my procedure like it and it works now as i wished..thanks for your help...
|
|
|
|
|
We have been upgrading our development IDE's to VStudio 2010. We generate reports for sql server currently using vstudio 2005. Is there a way for newer VStudio's to deploy the reports so sql server 2005 can understand them, so far we don't seem to see how.
|
|
|
|
|
Hi
I would like to share cool feature of SQL Server with you guys like:
Please use the following query when concatenate two or more rows with comma (or any you want) in a single string.
SELECT STUFF((SELECT ',' + RTRIM(RegionDescription) FROM Region FOR XML PATH('')),1,1,'') AS 'Regions'
Please do let me know, if you have any doubt.
Please provide "Vote" if this would be helpful.![Rose | [Rose]](https://www.codeproject.com/script/Forums/Images/rose.gif)
Thanks,
Imdadhusen
sunaSaRa Imdadhusen
+91 99095 44184
+91 02767 284464
|
|
|
|
General 
News 
Suggestion 
Question 
Bug 
Answer 
Joke 
Praise 
Rant 
Admin 
Submit an article or tip
