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?

Answer 1

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

Answer 2

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";

Answer 3

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

Answer 4

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

Answer 5

you must do something like this

select top 1 a.Weight as aWeight,
(select top 1 b.Age from Peeps b order by Age desc)as bAge 
from Peeps a order by Weight desc