How do I perform a GROUP BY on an aliased column in SQL Server?
You pass the expression you want to group by rather than the alias
SELECT LastName + ', ' + FirstName AS 'FullName'
FROM customers
GROUP BY LastName + ', ' + FirstName
Select rows with same id but different value in another column
This ought to do it:
SELECT *
FROM YourTable
WHERE ARIDNR IN (
SELECT ARIDNR
FROM YourTable
GROUP BY ARIDNR
HAVING COUNT(*) > 1
)
The idea is to use the inner query to identify the records which have a ARIDNR
value that occurs 1+ times in the data, then get all columns from the same table based on that set of values.
Get top 1 row of each group
;WITH cte AS
(
SELECT *,
ROW_NUMBER() OVER (PARTITION BY DocumentID ORDER BY DateCreated DESC) AS rn
FROM DocumentStatusLogs
)
SELECT *
FROM cte
WHERE rn = 1
If you expect 2 entries per day, then this will arbitrarily pick one. To get both entries for a day, use DENSE_RANK instead
As for normalised or not, it depends if you want to:
- maintain status in 2 places
- preserve status history
- ...
As it stands, you preserve status history. If you want latest status in the parent table too (which is denormalisation) you'd need a trigger to maintain "status" in the parent. or drop this status history table.
Group by minimum value in one field while selecting distinct rows
How about something like:
SELECT mt.*
FROM MyTable mt INNER JOIN
(
SELECT id, MIN(record_date) AS MinDate
FROM MyTable
GROUP BY id
) t ON mt.id = t.id AND mt.record_date = t.MinDate
This gets the minimum date per ID, and then gets the values based on those values. The only time you would have duplicates is if there are duplicate minimum record_dates for the same ID.
Related Topics
Snowflake: "SQL Compilation Error:... Is Not a Valid Group by Expression"
Find Start and Stop Date for Contiguous Dates in Multiple Rows
Tree Structure in SQL in Oracle.How to Show Tree,Child Nodes and Parent Nodes in SQL Oracle
Bigquery: How to Group and Count Rows Within Rolling Timestamp Window
Postgresql:How to Select Top N Percent(%) Entries from Each Group/Category
Create Temp Table with Range of Numbers
Can SQL Profiler Display Return Result Sets Alongside the Query
How to Create SQL Synonym or "Alias" for Database Name
How to Restore SQL Server 2008 Backup in SQL Server 2005
A Reliable Way to Verify T-SQL Stored Procedures
Use Google Bigquery to Build Histogram Graph
#1222 - the Used Select Statements Have a Different Number of Columns
Oracle: Is There Any Logical Reason Not to Use Parallel Execution with Subqueries in the Select List
How to Get the Latest Record for Each Item in Cosmosdb Using SQL
Returning Multiple Rows from Querying Xml Column in SQL Server 2008