The traditional solution, the one you may find in books, is to do a self join: first find that "max date per group" of yours, then join to self table on rows with said max date.
However, some hacks allow you to avoid that. Consider the following query:
SELECT
MAX(date_added) AS date_added,
SUBSTRING_INDEX(GROUP_CONCAT(some_column ORDER BY date_added DESC), ',', 1) AS some_column,
SUBSTRING_INDEX(GROUP_CONCAT(another_column ORDER BY date_added DESC), ',', 1) AS another_column
FROM t
GROUP BY whatever
GROUP_CONCAT
is an aggregation function which implodes values onto one string. It allows for ORDER BY
, which we utilize via ORDER BY date_added DESC
so as to implode our desired value first. We then slice up the first token in the string via SUBSTRING_INDEX
.
The downside here (apart from making the query quite the frightening appearance) is that your numerical values are transformed into texts. Typically no big deal with SQL, but please be aware.
See also my old post: Selecting a specific non aggregated column data in GROUP BY
There's another option where you do a semi-self-join, a much lighter one; you will have to give up usage of index. It's quite long to describe; it still uses GROUP_CONCAT
and SUBSTRING_INDEX
, but only for the purpose of creating a derived table with only relevant keys. This derived table is then joined with original table. See an example in SQL: selecting top N records per group, another solution.
Your idea is right but you need HAVING COUNT(DISTINCT design) > 1
Like this:
WITH multi_design_perfs AS
(SELECT b.perf_id
FROM perf_ticket_type b
GROUP BY b.perf_id
HAVING COUNT(DISTINCT b.design) > 1
)
SELECT m.perf_id,
STUFF((select ', ' + CAST(b.design AS varchar(10))
FROM perf_ticket_type b
WHERE b.perf_id = m.perf_id
ORDER BY b.design
FOR XML PATH(''), TYPE).value('.','varchar(max)'),1,2,'')
FROM multi_design_perfs m
;
Best Answer
I assume you want all rows of the group(s) where at least one row of the group has
sLossCode = 'REVSTD'
. In this case, you don't needGROUP BY
. You can use either a self join (but that would only work under certainUNIQUE
constraint requirements) or anEXISTS
subquery: