Select a Column in SQL Not in Group By

SELECT a column not in GROUP BY clause

There is no need for using a subquery or nested query. You can simply GROUP By Year field and use HAVING COUNT(Year)=1 to find the required rows. So, the applicable query will be:

SELECT Year, Event
FROM table_name
GROUP BY Year
HAVING COUNT(Year)=1

You can find the executable solution sample at:

http://sqlfiddle.com/#!9/b47044/11

Logic:
When you group by Yearit aggregates all rows with same year. So, count will be 2 for 2011.

You can check this by running:

SELECT Year, Event, COUNT(Year) as event_count
FROM table_name
GROUP BY Year

You can see this intermediate step in execution, at: http://sqlfiddle.com/#!9/b47044/10

This above solution will only work for MySQL version < 5.7. For higher versions find the solution below.

For 5.7 and greater the ONLY_FULL_GROUP_BY SQL mode is enabled by default so this will fail. Either you can update this mode( Refer answers under SELECT list is not in GROUP BY clause and contains nonaggregated column .... incompatible with sql_mode=only_full_group_by ) or alternatively you can use ANY_VALUE() function to refer to the non-aggregated column, so update query that will work in MySQL 5.7 and greater is:

SELECT Year, ANY_VALUE(Event)
FROM table_name
GROUP BY Year
HAVING COUNT(Year)=1;

You can find executable example at: https://paiza.io/projects/e/tU-7cUoy3hQUk2A7tFfVJg

Reference: https://docs.oracle.com/cd/E17952_01/mysql-5.7-en/miscellaneous-functions.html#function_any-value

GROUP BY Works with Select columns not in GROUP BY

This syntax is allowed and documented in SQLite: Bare columns in an aggregate query.

The columns FirstName, LastName, City, Email are called bare columns.

Such columns get an arbitrary value with the exception of the case where one (and only this one) of MIN() or MAX() is used. In this case the values of the bare columns are taken from the row that contains then min or max aggregated value.

In any case be careful when you use this syntax because you would get unexpected results.

Efficiently Include Column not in Group By of SQL Query

You can try something like this:

   ;WITH GroupedData AS
   (
       SELECT FkId, COUNT(FkId) As FkCount
       FROM B 
       GROUP BY FkId
   ) 
   SELECT gd.*, a.Name
   FROM GroupedData gd
   INNER JOIN dbo.A ON gd.FkId = A.FkId

Create a CTE (Common Table Expression) to handle the grouping/counting on your Table B, and then join that result (one row per FkId) to Table A and grab some more columns from Table A into your final result set.

Select column not on a group by statement

select year, max(round) as max_round, 
       max(raceid) keep (dense_rank last order by round) as raceid
from   races
group  by year
order  by year
;

Documentation for first and last function:

https://docs.oracle.com/database/121/SQLRF/functions074.htm#SQLRF00641

How do we select a column that is not in a group by clause

Your problem lies on your WHERE clause, on the 3rd condition:

AND T000.hr_efec_dati = max(T000.hr_efec_dati)

In here you are referencing the aggregate function MAX. The WHERE clause filters data before starting any grouping, so you can't use aggregate functions in here, that's what the HAVING clause is for.

Remove that condition from the WHERE clause and it will work (you already have it on the HAVING clause).

For your question, there are only 2 ways of selecting a column not being mentioned in a GROUP BY:

It's an aggregate function like MAX, MIN, AVG (or a custom one).
It's a constant value (hard-coded or @variable), or an expression that returns a constant value and does not reference any column not grouped by.

If you want to see column values for a particular group or groups, you should use the result of the grouping set and join them again with your table through the grouped by columns. Like this:

;WITH GroupedValues AS
(
    SELECT -- Remove the DISTINCT if you are already grouping by the same columns
        T012.hr_empl_code
        ,T012.hr_taxs_code
        ,max(T012.hr_efec_dati) AS last_hr_efec_dati
    FROM hrtempnm T000
    INNER JOIN hrtemptx T012 ON T000.hr_empl_code = T012.hr_empl_code
    WHERE T000.hr_stus_code LIKE 'e%'
        AND T000.hr_stus_code <> 'epla'
    GROUP BY T012.hr_empl_code
        ,T000.hr_efec_dati
        ,T012.hr_taxs_code
    HAVING T000.hr_efec_dati = max(T000.hr_efec_dati)
    -- remove the ORDER BY when used in a subquery (without TOP)
)
SELECT
    T012.hr_empl_code,
    T012.hr_taxs_code,

    UnGroupedColumn1,
    UnGroupedColumn2

FROM
    hrtempnm T000
    INNER JOIN hrtemptx T012 ON T000.hr_empl_code = T012.hr_empl_code
    INNER JOIN GroupedValues AS G ON 
        T012.hr_empl_code = G.hr_empl_code AND
        T012.hr_taxs_code = G.hr_taxs_code

Oracle SQL select rows that are not in GROUP BY clause

You can use analytical functions:

SELECT first_name, second_name, score, COUNT(*)  over (partition by score) FROM tab ;

Options for SELECTing a column not in GROUP BY clause?

The typical solution is to use ROW_NUMBER() to identify the rows you want.

For example:

select *
from (
  select a.*,
    row_number() over(partition by race order by age desc) as rn
  from animal a
) x
where rn = 1

Bigquery - Select a column with not grouping them in group by clause

Technically, you can envelope device_category and user_type with ANY_VALUE or MAX or MIN:

 SELECT
  month, 
  year, 
  ANY_VALUE(device_category), 
  ANY_VALUE(user_type), 
  product_name, 
  round(sum(item_revenue),2) as item_revenue 
FROM 
  `ProjectName.DatasetName.GA_REPORT_3_*` 
where 
  _table_suffix between '20201101' and '20210131' 
  and channel_grouping = 'Organic Search' 
group by 
  month, 
  year, 
  channel_grouping, 
  product_name 
order by 
  item_revenue desc;