How to use group by with union in t-sql

sql sql-server tsql group-by union
Just a learner picture Just a learner · Oct 22, 2009 · Viewed 134k times · Source

How can I using group by with union in t-sql? I want to group by the first column of a result of union, I wrote the following sql but it doesn't work. I just don't know how to reference the specified column (in this case is 1) of the union result. great thanks.

SELECT  *
FROM    ( SELECT    a.id ,
                    a.time
          FROM      dbo.a
          UNION
          SELECT    b.id ,
                    b.time
          FROM      dbo.b
        )
GROUP BY 1

Answer

Thomas picture Thomas · Oct 29, 2009

You need to alias the subquery. Thus, your statement should be:

Select Z.id
From    (
        Select id, time
        From dbo.tablea
        Union All
        Select id, time
        From dbo.tableb
        ) As Z
Group By Z.id

Related questions

How to return a incremental group number per group in SQL

I would like create a data query in SQL to incrementally number groups of rows, grouped on a common datetime and keep the "group numbers" incrementing on the next datetime and so on. These "group numbers" must not reset for …

Read More
SQL Server Query for Rank (RowNumber) and Groupings

I have a table that has some columns: User, Category, Value And I want to make a query that will give me a ranking, of all the users by the value, but reset for the category. Example: user1 CategoryA 10 user2 …

Read More
How do I UPDATE from a SELECT in SQL Server?

In SQL Server, it is possible to insert rows into a table with an INSERT.. SELECT statement: INSERT INTO Table (col1, col2, col3) SELECT col1, col2, col3 FROM other_table WHERE sql = 'cool' Is it also possible to update a …

Read More