SQL Server - Multiple Select with Duplicate Names

Question

I'm trying to grab some data from my SQL database as below;

USE exampleDatabase
SELECT TOP(1) [Name] FROM [Peeps] ORDER BY [Weight] DESC
SELECT TOP(1) [Name] FROM [Peeps] ORDER BY [Age] DESC

The problem is when I read the data I get an error 'Name'.

Dim byWeight As String = sqlReader.GetValue(sqlReader.GetOrdinal("Name"))
Dim byAge As String = sqlReader.GetValue(sqlReader.GetOrdinal("Name"))

How would I read this data as above considering I can't use name twice?

Thomas · Accepted Answer · 2010-03-07 16:48:52Z

1

I think you are missing a semi-colon after the first SELECT statement. Here's a sample app I put together (note the semicolon in my sql statement):

var sql = "Select TOP 1 name from sys.columns;"
          + "Select TOP 1 name from sys.objects";

var firstname = string.Empty;
var secondname = string.Empty;

var connString = ConfigurationManager.ConnectionStrings["Test"].ConnectionString;
using ( var conn = new SqlConnection( connString ) )
{
    conn.Open();
    using ( var cmd = new SqlCommand( sql, conn ) )
    {
        var reader = cmd.ExecuteReader();
        if ( reader == null )
            return;
        if ( reader.Read() )
            firstname = reader.GetString( reader.GetOrdinal( "Name" ) );

        reader.NextResult();
        if ( reader.Read() )
            secondname = reader.GetString( reader.GetOrdinal( "Name" ) );

    }
}

Response.Write( firstname + "<br />" );
Response.Write( secondname + "<br />" );

You can achieve the same goal as the semi-colon by using the "GO keyword like so:

    var sql = "Select TOP (1) name from sys.columns GO "
              + "Select TOP (1) name from sys.objects";

edited Mar 7, 2010 at 16:48

answered Mar 7, 2010 at 1:29

Thomas

64.8k12 gold badges98 silver badges142 bronze badges

Sign up to request clarification or add additional context in comments.

2 Comments

Bram Over a year ago

Thanks. The semi-colons made no difference. I tested and the Select statement works fine in SQL Management Studio.

Thomas Over a year ago

Yes, but you aren't using these select statements in SMS. You are trying to use this through ADO.NET and ADO.NET gets confused if you do not provide a means to terminate the select statements. In my example, if I remove the semi-colon, it throws a runtime error.

tzaman · Accepted Answer · 2010-03-07 01:25:38Z

0

You can use the 'as' keyword to rename columns in your results, like so:

SELECT TOP(1) [Name] AS ByWeight FROM [Peeps] ORDER BY [Weight] DESC

answered Mar 7, 2010 at 1:25

tzaman

48k11 gold badges93 silver badges118 bronze badges

2 Comments

Bram Over a year ago

Thanks - I've tried this and it make no difference. Visual Studio throws an exception with the message 'ByWeight'.

Hogan Over a year ago

The message is ByWeight? Really?

Hogan · Accepted Answer · 2010-03-07 03:56:39Z

0

hmmm... well you could make it one select statement....

USE exampleDatabase

SELECT W.[Name] AS W_Name,  A.[Name] AS A_Name FROM
(SELECT TOP(1) [Name] FROM [Peeps] ORDER BY [Weight] DESC) W 
JOIN (SELECT TOP(1) [Name] FROM [Peeps] ORDER BY [Age] DESC) A

answered Mar 7, 2010 at 3:56

Hogan

70.7k10 gold badges83 silver badges119 bronze badges

Comments

Philip Fourie · Accepted Answer · 2010-03-07 04:52:46Z

0

What if you UNION your SQL together into one result set?

USE exampleDatabase
SELECT TOP(1) [Name] FROM [Peeps] ORDER BY [Weight] DESC
UNION ALL
SELECT TOP(1) [Name] FROM [Peeps] ORDER BY [Age] DESC

answered Mar 7, 2010 at 4:52

Philip Fourie

118k11 gold badges69 silver badges84 bronze badges

Collectives™ on Stack Overflow

SQL Server - Multiple Select with Duplicate Names

4 Answers 4

2 Comments

2 Comments

Comments

Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

4 Answers 4

2 Comments

2 Comments

Comments

Comments

Your Answer

Sign up or log in

Post as a guest

Related